Flatten unique columns in crosstab query

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

SQL Server 2005 Forums

Transact-SQL (2005)

Flatten unique columns in crosstab query

Author

Topic

SimonMallinson
Starting Member

3 Posts

Posted - 2008-08-19 : 12:28:14

Hi all,

I have a crosstab query which has come from a table of hierarchical data e.g.

Item----code----description----value
AB1.....12......Width..........100
AB1.....13......Height.........200
AB1.....14......Colour.........Red
AB2.....12......Width..........120
AB2.....13......Height.........210
AB2.....14......Colour.........Blue

which looks a bit like this :

Item-----Width----Height----Colour
AB1......100......null......null
AB1......null.....200.......null
AB1......null.....null......Red

etc

Is there any way that I can get :

Item----Width----Height----Colour
AB1.....100......200.......Red
AB2.....120......210.......Blue

The reason is I need to get an SSRS report per line out for each item and am struggling because of the null values.

If there's another way round it I'll gladly take that as well! I'm stuck!

Thanks very much for your time - please feel free to get me to clarify anything I haven't explained properly. I'm using sql server 2005.

Regards

Simon

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-08-19 : 12:34:30

[code]SELECT Item,
MAX(CASE WHEN description ='Width' THEN value ELSE NULL END) AS Width,
MAX(CASE WHEN description ='Height' THEN value ELSE NULL END) AS Height,
MAX(CASE WHEN description ='Colour' THEN value ELSE NULL END) AS Colour
FROM YourQuery
GROUP BY Item[/code]

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-08-19 : 12:36:54

or use pivot

SELECT Item,
       [Width],
       [Height],
       [Colour]
FROM (SELECT Item,code,description,value FROM Yourquery)m
PIVOT (MAX(value) FOR description IN ([Width],[Height],[Colour]))p

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-08-19 : 12:41:13

And another way is to do this in your ssrs report with a matrix. just place give row grouping as item field and column grouping based on description field.

SimonMallinson
Starting Member

3 Posts

Posted - 2008-08-19 : 15:07:32

Thanks very much for the help - I will give those a go when I get to work tomorrow morning!

Regards

Simon

SimonMallinson
Starting Member

3 Posts

Posted - 2008-08-20 : 07:39:58

Worked a treat - thanks.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-08-20 : 07:41:54