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: 3118

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 :

CompanyIDYearRev_IDMetarialID1888574MetarialID1888575MetarialID1888576MetarialID1888577supplier1888574supplier1888575supplier1888576supplier1888577
1039109202133982083.1897.0592.8795.7480.1899.0094.8796.74
1039152202033989880.1810098.8793.7479.1899.0096.8790.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: 1004406 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)

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

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

David Burrows SSC Guru Points: 65143 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: 1004406 More actions · Answer 4

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?

--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)

David Burrows SSC Guru Points: 65143 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 3 years ago by David Burrows.

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