How to make data pivot dynamically to materialper and suplierperc both ?

Question

Post reply

How to make data pivot dynamically to materialper and suplierperc both ?

ahmed_elbarbary.2010

Hall of Fame

Points: 3070

December 23, 2021 at 12:37 am

#3966533

I work on sql server 2014 i need to pivot data for multi column as supplier percentage and material percentage

my sample data as below :

I work on sql server 2014 i need to pivot data for multi column as supplier percentage and material percentage

my sample data as below :


CREATE TABLE [dbo].[TempMaterial](
[CompanyID] [bigint] NOT NULL,
[Year] [int] NOT NULL,
[Rev_ID] [int] NULL,
[MetarialID] [int] NULL,
[Metarialperc] [float] NULL,
[SupplierPerc] [decimal](18, 2) NULL
) ON [PRIMARY]

GO
---select * from [dbo].[TempMaterial]
insert into [dbo].[TempMaterial](CompanyID,Year,Rev_ID,MetarialID,Metarialperc,SupplierPerc)
select 1039152, 2020,339898,1888574,80.18,79.18
union
select 1039152, 2020,339898,1888575,100,99.00
union
select 1039152, 2020,339898,1888576,98.87,96.87
union
select 1039152, 2020,339898,1888577,93.74,90.74
union


select 1039109, 2021,339820,1888574,83.18,80.18
union
select 1039109, 2021,339820,1888575,97.05,99.00
union
select 1039109, 2021,339820,1888576,92.87,94.87
union
select 1039109, 2021,339820,1888577,95.74,96.74

select * from [dbo].[TempMaterial]

Expected result as below

expected result as below :

CompanyID	Year	Rev_ID	MetarialID1888574	MetarialID1888575	MetarialID1888576	MetarialID1888577	supplier1888574	supplier1888575	supplier1888576	supplier1888577
1039109	2021	339820	83.18	97.05	92.87	95.74	80.18	99.00	94.87	96.74
1039152	2020	339898	80.18	100	98.87	93.74	79.18	99.00	96.87	90.74

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 1

Do you want the output just like what you posted or do you want it in columns? And, can you clean up what the output is supposed to look like? The first row isn't making sense in it's current form Perhaps the display of a spreadsheet would help us.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
"Change is inevitable... change for the better is not".

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions

ahmed_elbarbary.2010 Hall of Fame Points: 3070 More actions · Answer 2

output as below

CompanyID	Year	Rev_ID	MetarialID1888574	MetarialID1888575	MetarialID1888576	MetarialID1888577	supplier1888574	supplier1888575	supplier1888576	supplier1888577
1039109	2021	339820	83.18	97.05	92.87	95.74	80.18	99.00	94.87	96.74
1039152	2020	339898	80.18	100	98.87	93.74	79.18	99.00	96.87	90.74

my result will be

company and year and rev then pivot first Colum based on material id ,pivot second Colum based on material id

with another meaning

material id is four for first column material percentage,material id is four for second column supplier percentage

material percentage for every material related,supplier percentage for every material related

David Burrows SSC Guru Points: 65111 More actions · Answer 3

Static version

SELECT CompanyID,[Year],Rev_ID
,MAX(CASE WHEN MetarialID=1888574 THEN Metarialperc ELSE 0.0 END) AS [MetarialID1888574]
,MAX(CASE WHEN MetarialID=1888575 THEN Metarialperc ELSE 0.0 END) AS [MetarialID1888575]
,MAX(CASE WHEN MetarialID=1888576 THEN Metarialperc ELSE 0.0 END) AS [MetarialID1888576]
,MAX(CASE WHEN MetarialID=1888577 THEN Metarialperc ELSE 0.0 END) AS [MetarialID1888577]
,MAX(CASE WHEN MetarialID=1888574 THEN Supplierperc ELSE 0.0 END) AS [Supplier1888574]
,MAX(CASE WHEN MetarialID=1888575 THEN Supplierperc ELSE 0.0 END) AS [Supplier1888575]
,MAX(CASE WHEN MetarialID=1888576 THEN Supplierperc ELSE 0.0 END) AS [Supplier1888576]
,MAX(CASE WHEN MetarialID=1888577 THEN Supplierperc ELSE 0.0 END) AS [Supplier1888577]
FROM dbo.TempMaterial
GROUP BY CompanyID,[Year],Rev_ID;

Dynamic version

DECLARE @sql varchar(max);
SET @sql = 'SELECT CompanyID,[Year],Rev_ID ';

SELECT @sql += ',MAX(CASE WHEN MetarialID='+CAST(MetarialID as varchar(10))+' THEN Metarialperc ELSE 0.0 END) AS [MetarialID'+CAST(MetarialID as varchar(10))+']'
FROM dbo.TempMaterial
GROUP BY MetarialID
ORDER BY MetarialID ASC;

SELECT @sql += ',MAX(CASE WHEN MetarialID='+CAST(MetarialID as varchar(10))+' THEN Supplierperc ELSE 0.0 END) AS [Supplier'+CAST(MetarialID as varchar(10))+']'
FROM dbo.TempMaterial
GROUP BY MetarialID
ORDER BY MetarialID ASC;

SET @sql += 'FROM FROM dbo.TempMaterial ';
SET @sql += 'GROUP BY CompanyID,[Year],Rev_ID;';

EXECUTE (@sql);

Far away is close at hand in the images of elsewhere.
Anon.

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 4

ahmed_elbarbary.2010 wrote:

output as below
CompanyID	Year	Rev_ID	MetarialID1888574	MetarialID1888575	MetarialID1888576	MetarialID1888577	supplier1888574	supplier1888575	supplier1888576	supplier1888577
1039109	2021	339820	83.18	97.05	92.87	95.74	80.18	99.00	94.87	96.74
1039152	2020	339898	80.18	100	98.87	93.74	79.18	99.00	96.87	90.74
my result will be

company and year and rev then pivot first Colum based on material id ,pivot second Colum based on material id

with another meaning

material id is four for first column material percentage,material id is four for second column supplier percentage

material percentage for every material related,supplier percentage for every material related

It doesn't make sense to me as to why there are no delimiters in the first row. At least you use periods in the other rows as delimiters.

I'm just curious... why are there no delimiters int he first row of output?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
"Change is inevitable... change for the better is not".

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions

David Burrows SSC Guru Points: 65111 More actions · Answer 5

Jeff Moden wrote:

ahmed_elbarbary.2010 wrote:
output as below
CompanyIDYearRev_IDMetarialID1888574MetarialID1888575MetarialID1888576MetarialID1888577supplier1888574supplier1888575supplier1888576supplier1888577
1039109202133982083.1897.0592.8795.7480.1899.0094.8796.74
1039152202033989880.1810098.8793.7479.1899.0096.8790.74
my result will be

company and year and rev then pivot first Colum based on material id ,pivot second Colum based on material id

with another meaning

material id is four for first column material percentage,material id is four for second column supplier percentage

material percentage for every material related,supplier percentage for every material related
It doesn't make sense to me as to why there are no delimiters in the first row. At least you use periods in the other rows as delimiters.

I'm just curious... why are there no delimiters int he first row of output?

The dots are decimal points I thing any delimiters or formatting was lost in copy/paste.

Actually I think it is a display problem, if you use quote button the data is displayed with tab delimiters and if you double click on the box it shows the edit box and the language as SQL.

Weird 🙁

This reply was modified 2 years, 9 months ago by David Burrows.

Far away is close at hand in the images of elsewhere.
Anon.