Rows to Columns

[gbobbie]

I am trying to create a SQL query (SQL Server) that will take what is currently in rows and spread out over a column. For example, the data currently displays like this:
lookup_key group_name
100 CARDIOVAS
100 CARDIOVASMS
101 AUDIOLOASS
101 AUDIOLOASSPA
101 AUDIOLOASSMA

I would like to have the data display as follows:

lookup_key group_name1 group_name2 group_name3
100 CARDIOVAS CADRIOVASMS
101 AUDIOLOASS AUDIOLOASSPA AUDIOLASSMA

Does anyone have any ideas? Please let me know if you need further info.

 

[RiverGuy]

You should do this on the front end reporting solution. If you're using SQL Server Reporting Services, then use the Matrix component.

 

[MKP900]

You can use a CASE statement for this.

In an example grouping by months, you could try something like...

SELECT state,
--GET JANUARY TOTAL
CASE datepart(mm,DateField) when 1 then
SUM(AmountField)
ELSE 0
END JanuaryTotal,
--GET FEBRUARY TOTAL
CASE datepart(mm,DateField) when 2 then
SUM(AmountField)
ELSE 0
END FebruaryTotal,
--GET MARCH TOTAL
CASE datepart(mm,DateField) when 3 then
SUM(AmountField)
ELSE 0
END MarchTotal,
...and so on.

You can use this general idea for so many things! For more info do an internet search for cross-tabs and SQL. You will find more examples.

HTH
-mk