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

Question

0.00/5 (No votes)

See more:

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

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

so can you help me make it dynamically please for both column

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

What I have tried:

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

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

Posted 22-Dec-21 13:45pm

ahmed_sa

Updated 29-Dec-21 1:26am

v3

Add a Solution

Comments

k5054 22-Dec-21 21:16pm

Your expected results got lost somewhere. Please update your question.

CHill60 23-Dec-21 6:27am

How have you pivoted the data for just one column?

CHill60 23-Dec-21 6:29am

Last month you were using SQL 2012. If you were going to upgrade why didn't you call all the way instead of just to 2014?

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

CHill60 · Answer 1 · 2021-12-29T01:26:00

First step is to UNPIVOT your starting data (note I am using a temporary table for your table in these examples). Here is the reference documentation Using PIVOT and UNPIVOT - SQL Server | Microsoft Docs[^]

SQL

SELECT CompanyID,Year,Rev_ID,MetarialID, ValueType, x 
into #temp1
FROM   
   (SELECT CompanyID,Year,Rev_ID,MetarialID, Metarialperc,SupplierPerc
   FROM #TempMaterial) p  
UNPIVOT  
   (x FOR ValueType IN   
      (Metarialperc,SupplierPerc)  
)AS unpvt;

This puts these values into table #temp1

CompanyID	Year	Rev_ID	MetarialID	ValueType		x
1039109		2021	339820	1888574		Metarialperc	83.18
1039109		2021	339820	1888574		SupplierPerc	80.18
1039109		2021	339820	1888575		Metarialperc	97.05
1039109		2021	339820	1888575		SupplierPerc	99.00
1039109		2021	339820	1888576		Metarialperc	92.87
1039109		2021	339820	1888576		SupplierPerc	94.87
1039109		2021	339820	1888577		Metarialperc	95.74
1039109		2021	339820	1888577		SupplierPerc	96.74
1039152		2020	339898	1888574		Metarialperc	80.18
1039152		2020	339898	1888574		SupplierPerc	79.18
1039152		2020	339898	1888575		Metarialperc	100.00
1039152		2020	339898	1888575		SupplierPerc	99.00
1039152		2020	339898	1888576		Metarialperc	98.87
1039152		2020	339898	1888576		SupplierPerc	96.87
1039152		2020	339898	1888577		Metarialperc	93.74
1039152		2020	339898	1888577		SupplierPerc	90.74

Step 2 is to provide an interim step to work out our final column names

SQL

select CompanyID,Year,Rev_ID,valueType, MetarialID, replace(valueType,'perc','') + cast(MetarialID as varchar(255)) as finalCol, x
into #temp2
FROM #temp1

Table #temp2 now contains the same data as #temp1 but with an additional column finalcol containing values Metarial1888574, Metarial1888575, Metarial1888576, Metarial1888577, supplier1888574, supplier1888575, supplier1888576, supplier1888577

I can now PIVOT table #temp2 to get the expected values you have listed

SQL

select * from
(
	select CompanyID,Year,Rev_ID,finalCol, x
	FROM #temp2
) src1
PIVOT
(
	max(x) for finalCol in (Metarial1888574, Metarial1888575, Metarial1888576, Metarial1888577, 
		supplier1888574,supplier1888575, supplier1888576, supplier1888577)
) pvt1

Assuming your use of the word "dynamically" in your question means literally that, then you are going to have to generate this as dynamic SQL - There are several examples on how to do that on dynamic query in sql - Google Search[^]
There is an example of generating the list of column names (as well as some simple Dynamic SQL) in my article Processing Loops in SQL Server[^]