Dynamic Pivot for multiple columns

• gksharmaajmer

  Ten Centuries

  Points: 1120

  More actions

  July 17, 2013 at 6:57 am

  #98823

  Hi,

  Here is my friend's sql server version info :

  Microsoft SQL Server 2000 - 8.00.2039 (Intel X86) May 3 2005 23:18:38 Copyright (c) 1988-2003 Microsoft Corporation Enterprise Edition on Windows NT 5.2 (Build 3790: Service Pack 2)

  Table structure and sample insert statements :

  create table consumption (

  code varchar(6),

  prodname varchar(50),

  department varchar(20),

  netqty decimal(10,2),

  netmrp decimal(10,2)

  )

  insert into consumption values ('U00180','USG JELLY IM-K','SONO',11.4,130.40)

  insert into consumption values ('U00280','UNIDINE 1 LITRE SOLUTION','AKD',1.4,10.40)

  insert into consumption values ('V02401','VOLINI GEL 50GM','PHYSIOTHERAPY',8,15)

  insert into consumption values ('V02402','X RAY FILM DIGITAL 14"X 17"','MRI',3,26.40)

  insert into consumption values ('U00380','TROPAC P DROPS ','AKD',21.46,56.78)

  insert into consumption values ('U00380','TROPAC P DROPS ','AKD',10,10)

  Table Data :

  code prodnamedepartmentnetqtynetmrp

  U00180USG JELLY IM-KSONO11.40130.40

  U00280UNIDINE 1 LITRE SOLUTIONAKD 1.40 10.40

  V02401VOLINI GEL 50GMPHYSIOTHERAPY 8.00 15.00

  V02402X RAY FILM DIGITAL 14"X 17"MRI 3.00 26.40

  U00380TROPAC P DROPS AKD21.46 56.78

  U00380TROPAC P DROPS AKD10.00 10.00

  Required Output please :

  CODE PRODNAME AKD MRI PHYSIOTHERAPY SONO

  NET QTY NET MRP NET QTY NET MRP NET QTY NET MRP NET QTY NET MRP

  U00180USG JELLY IM-K 11.40 130.40

  U00280UNIDINE 1 LITRE SOLUTION 1.40 10.40

  U00380TROPAC P DROPS 31.46 66.78

  V02401VOLINI GEL 50GM 8.00 15.00

  V02402X RAY FILM DIGITAL 14"X 17" 3.00 26.40

  I have google and implemented one column pivot i.e. NET QTY column by the help of below link :

  http://www.sqlteam.com/article/dynamic-cross-tabs-pivot-tables

  But here he needs to pivot more than one column with dynamic pivoting. I am sure, it has been asked many times, if you please share any link and/or solution, we shall be thankful to you.

  Kindly let me know, if I am unclear in my question and/or needs to provide more info.

  Thanks and Regards

  Girish Sharma

• Luis Cazares

  SSC Guru

  Points: 183706

  More actions

  July 17, 2013 at 8:58 am

  #1633330

  There are 2 great articles from Jeff Moden. Here's the part on dynamic pivoting

  Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs[/url]

  Luis C.
  General Disclaimer:
  Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

  How to post data/code on a forum to get the best help: Option 1 / Option 2

• gksharmaajmer

  Ten Centuries

  Points: 1120

  More actions

  July 17, 2013 at 9:00 pm

  #1633505

  I solved the question with the help of below link :

  http://stackoverflow.com/questions/14694691/sql-server-pivot-table-with-multiple-column-aggregates

  declare @sql nvarchar(max)

  select @sql='SELECT min(t.code) As CODE,PRODNAME'

  DECLARE @ColumnTemplate NVARCHAR(MAX)

  select @ColumnTemplate='SUM(CASE WHEN t.department=''{department}'' THEN t.netqty ELSE 0 END) AS "{department}_NetQty"

  ,SUM(CASE WHEN t.department=''{department}'' THEN t.netmrp ELSE 0 END) AS "{department}_NetMrp"'

  SELECT @sql=@Sql+CHAR(13)+','+REPLACE(@ColumnTemplate, '{department}', REPLACE(c.name,'''',''''''))

  FROM (

  SELECT DISTINCT t.department AS name

  FROM consumption t

  ) c

  SELECT @sql=@Sql+'FROM consumption t GROUP BY t.prodname order by code;'

  --PRINT @sql;

  EXEC(@Sql);

  Regards

  Girish Sharma