How to Pivot column to rows within a Group

Question

Post reply

How to Pivot column to rows within a Group

prateek.one4u

Grasshopper

Points: 10
More actions
December 18, 2014 at 4:20 am

#313181

Hi Team,
I need to convert the column to rows but within group. example
Group Name Value
p a 1
p b 2
p c 3
p d 4
q a 5
q b 6
q d 7
r a 8
r b 9
r c 10
r d 11
This need to be transposed to :
Group a b c d
p1234
q56NULL7
r891011
Any help would be great !
Thanks

Viewing 4 posts - 1 through 3 (of 3 total)

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

Mark Cowne One Orange Chip Points: 26952 More actions · Answer 1

CREATE TABLE #Temp([Group] CHAR(1),Name CHAR(1),Value INT)

INSERT INTO #Temp([Group],Name,Value)

SELECT 'p','a',1 UNION ALL

SELECT 'p','b',2 UNION ALL

SELECT 'p','c',3 UNION ALL

SELECT 'p','d',4 UNION ALL

SELECT 'q','a',5 UNION ALL

SELECT 'q','b',6 UNION ALL

SELECT 'q','d',7 UNION ALL

SELECT 'r','a',8 UNION ALL

SELECT 'r','b',9 UNION ALL

SELECT 'r','c',10 UNION ALL

SELECT 'r','d',11

SELECT [Group],

SUM(CASE WHEN Name = 'a' THEN Value END) AS [a],

SUM(CASE WHEN Name = 'b' THEN Value END) AS ,

SUM(CASE WHEN Name = 'c' THEN Value END) AS [c],

SUM(CASE WHEN Name = 'd' THEN Value END) AS [d]

FROM #Temp

GROUP BY [Group]

____________________________________________________

Deja View - The strange feeling that somewhere, sometime you've optimised this query before

How to get the best help on a forum

http://www.sqlservercentral.com/articles/Best+Practices/61537

velumurugan palanivel SSC Enthusiast Points: 120 More actions · Answer 2

CREATE TABLE #Temp([Group] CHAR(1),Name CHAR(1),Value INT)

INSERT INTO #Temp([Group],Name,Value) values

('p','a',1),

('p','b',2),

('p','c',3),

('p','d',4),

('q','a',5),

('q','b',6),

('q','d',7),

('r','a',8),

('r','b',9),

('r','c',10),

('r','d',11)

SELECT [Group],a,b,c,d FROM

(

SELECT [GROUP],Name,value FROM #Temp

) AS P

Pivot

(

sum(value)

for name in([a],,[c],[d])

) as pvt

order by pvt.[Group]

Jeff Moden SSC Guru Points: 1004304 More actions · Answer 3

For more information on how that works and an alternative along with some performance testing, please see the following article...

http://www.sqlservercentral.com/articles/T-SQL/63681/

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