dynamic pivot

Question

Here is my intial table t1 and final table t2

based on the parameter passed i need to pivot the table 

t1

RECORDER_ID	CHANNEL_NO	M	DATE	value
A	1	KW	11/13/2012 0:00	1072.8
A	2	KVAR	11/13/2012 0:00	447.12
A	1	KW	11/13/2012 0:45	1066.32
A	2	KVAR	11/13/2012 0:45	403.2
A	1	KW	11/13/2012 1:00	1084.32
A	2	KVAR	11/13/2012 1:00	419.04
b	1	KW	11/13/2012 0:00	1167.84
b	2	KVAR	11/13/2012 0:00	444.24
b	3	KW	11/13/2012 0:00	1159.92
b	4	KVAR	11/13/2012 0:00	436.32

t2

recorder_id	DATE	CHANNEL_1(kw)	CHANNEL_2(KVAR)
a	11/13/2012 0:00	1072.8	447.12
11/13/2012 0:00	1066.32	403.2
11/13/2012 0:45	1084.32	1084.32
if b is passed	DATE	CHANNEL_1(kw)	CHANNEL_2(KVAR)	channel_3(kw)	ch_4(kvar)
11/13/2012 0:00	1167	444	1159	436
CHANNEL NO MUST BE DISPLAYED BASED ON RECORDERS ID  AND
THEN PIVOTED BASED ON THE SELECTION MADE BY CUSTOMER

Answer 1

check this https://www.simple-talk.com/blogs/2007/09/14/pivots-with-dynamic-columns-in-sql-server-2005/

---

Please use Marked as Answer if my post solved your problem and use Vote As Helpful if a post was useful.

Answer 2

Hi,

Try this link for an example on dynamic pivoting - http://www.allaboutmssql.com/2012/10/sql-server-dynamic-pivoting.html

---

sathya - www.allaboutmssql.com ** Mark as answered if my post solved your problem and Vote as helpful if my post was useful **.

Answer 3

You can also create a dynamic pivot from the below query.

DECLARE @TEMP TABLE(RECORDER_ID CHAR(1),CHANNEL_NO INT,M VARCHAR(10),DATE DATETIME,[value] DECIMAL(10,2))
INSERT INTO @TEMP VALUES('A', 1,'KW','11/13/2012 0:00',1072.8 )
INSERT INTO @TEMP VALUES('A', 2,'KVAR','11/13/2012 0:00',447.12 ) 
INSERT INTO @TEMP VALUES('A', 1,'KW','11/13/2012 0:45',1066.32 ) 
INSERT INTO @TEMP VALUES('A', 2,'KVAR','11/13/2012 0:45',403.2 ) 
INSERT INTO @TEMP VALUES('A', 1,'KW','11/13/2012 1:00',1084.32 ) 
INSERT INTO @TEMP VALUES('A', 2,'KVAR','11/13/2012 1:00',419.04 ) 
INSERT INTO @TEMP VALUES('b', 1,'KW','11/13/2012 0:00',1167.84 ) 
INSERT INTO @TEMP VALUES('b', 2,'KVAR','11/13/2012 0:00',444.24 ) 
INSERT INTO @TEMP VALUES('b', 3,'KW','11/13/2012 0:00',1159.92 ) 
INSERT INTO @TEMP VALUES('b', 4,'KVAR','11/13/2012 0:00',436.32 ) 
;WITH CTE AS (SELECT *,'CHANNEL_' + CAST(CHANNEL_NO AS VARCHAR(1)) + '(' + M + ')' CHANNEL_M FROM @TEMP)
,CTE1 AS (
	SELECT RECORDER_ID,[DATE],[CHANNEL_1(KW)],[CHANNEL_2(KVAR)],[CHANNEL_3(KVAR)],[CHANNEL_4(KVAR)] FROM CTE
	PIVOT
	(MAX([VALUE]) FOR CHANNEL_M IN ([CHANNEL_1(KW)],[CHANNEL_2(KVAR)],[CHANNEL_3(KVAR)],[CHANNEL_4(KVAR)])
	) PVT
)
SELECT RECORDER_ID,[DATE],
MAX([CHANNEL_1(KW)]) [CHANNEL_1(KW)],
MAX([CHANNEL_2(KVAR)]) [CHANNEL_2(KVAR)],
MAX([CHANNEL_3(KVAR)]) [CHANNEL_3(KVAR)],
MAX([CHANNEL_4(KVAR)]) [CHANNEL_4(KVAR)]
FROM CTE1
GROUP BY RECORDER_ID,[DATE]
ORDER BY RECORDER_ID

---

Regards, RSingh

Answer 4

Thank you 

  I used dynamic pivoting to solve the problem.

Thank you all for the help.