Grouping on varchar columns - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Grouping on varchar columns

Author

Topic

Dave5498
Starting Member

1 Post

Posted - 2011-05-10 : 06:53:51

I am new to SQL so need a bit of help!

I have a table called: my_attributes

ProductID, At1, At2
1, TABLE, NULL
1, CHAIR, Wooden
1, DOOR, Old
2, TABLE, Garden
2, CHAIR, Metal
2, DOOR, NULL
3, TABLE, NULL
3, CHAIR, Rocker
3, DOOR, NULL

I need the product down the side and at1 along the top so I have done:

SELECT ProductID
,CASE WHEN [At1] = 'TABLE' THEN [At2] ELSE '' END AS [TABLE]
,CASE WHEN [At1] = 'CHAIR' THEN [At2] ELSE '' END AS [CHAIR]
,CASE WHEN [At1] = 'DOOR' THEN [At2] ELSE '' END AS [DOOR]
FROM my_attributes

But I end up with more than one line for each productID eg:

Product, TABLE, CHAIR, DOOR
1, '', '', ''
1, '', Wooden, ''
1, '', '', Old
etc

How can I get
Product, TABLE, CHAIR, DOOR
1, '', 'Wooden', 'Old'
2, 'Garden','Metal',''
etc

Something like a GROUP BY with doing any sums as all the values as varchars.

Many Thanks for your help.

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2011-05-10 : 07:07:26

You can't sum, but you can max
SELECT ProductID
,MAX(CASE WHEN [AT1] = 'TABLE' THEN [AT2] ELSE NULL END)
,....
FROM my_Attributes
GROUP BY ProductID

Jim

Everyday I learn something that somebody else already knew

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources