pivot? rows to columns

[larry2]

Hi folks,
I have a table example:
Date Team Score
200801 A 12
200801 B 14
200802 A 3
200803 C 5
200803 A 5

Date and team are not hardcoded, next week team D could be added etc.

I'd like to convert (pivot?) to
Date A B C
200801 12 14
200802 3
200803 5 5

is there a simple approach to this?
any help appreciated. Sorry don't know what this type of conversion is called.

thanks.

[r035198x]

Could you maybe explain what the result should represent?

[ck9663]

Maybe this helps.


-- CK

[larry2]

Maybe this helps.


-- CK

Yeah I've been reading through the online docs etc., and some nice books like 'SQL Cookbook' and 'the Art of SQL'. The problem with the example in the linked page is that '[164] as emp1 ... as emp2' the columns are hardcoded into the sql statement. Since the team in each row (A, B, C, etc.) will later include other teams, this won't work for me.

I can write some code to create new sql statements each time by a first pass to determine unique teams, then use a case statement, just thought there'd be a cleaner approach.

[larry2]

Could you maybe explain what the result should represent?

Sure!
The result is grouped by row.col1 (date), with row.col2 the new col1->n, and row.col3 the data.

If it helps, think of it as total points each month. In Jan Team A had 12 points. In Jan Team B had 14 points. In Feb Team A had 3. In March Team C joined and had 5 points. In March Team A had 5 points.

I don't want to hard code the teams into record columns as these will change over time and would require recoding. Could do T1->t20 etc. but that still forces limits, gotta recode for team 21.

Probably wasn't obvious from the previous message, here is the desired output again: Note I could end up with 1000's of records to process, maybe 20 or so teams. I added 0's instead of spaces to help display, either (0's or spaces) is okay.



Date__ | A | B | C |
200801 | 12 | 14 | 0 |
200802 | 3 | 0 | 0 |
200803 | 5 | 0 | 5 |

[larry2]

Well I'm about 90% there perhaps.

SELECT *
FROM
(Select TDate, Team, Score from Test) tts
PIVOT
(
SUM(Score) FOR Team IN (A,B,C)
) as pvt

Again only problem is the (A,B,C) I'm looking for a wildcard or something I can use here so I don't have to preselect the column names.

MS Access Wizard gave me this:
TRANSFORM Sum(Test.Score) AS SumOfScore
SELECT Test.TDate
FROM Test
GROUP BY Test.TDate
PIVOT Test.Team;

which doesn't require the columns names, but this doesn't translate to mssql.

Any other suggestions?

[BHTanna]

Try this store procedure..

CREATE PROCEDURE crosstab
@select varchar(8000),
@sumfunc varchar(100),
@pivot varchar(100),
@table varchar(100)
AS
DECLARE @sql varchar(8000), @delim varchar(1)
SET NOCOUNT ON
SET ANSI_WARNINGS ON

if exists(select name from sysobjects where name = '##pivotos' and type= 'U')
DROP TABLE ##pivotos
Print('SELECT ' + @pivot + ' AS pivot INTO ##pivotos FROM ' + @table + ' WHERE 1=2')

exec ('SELECT ' + @pivot + ' AS pivot INTO ##pivotos FROM ' + @table + ' WHERE 1=2')



EXEC ('INSERT INTO ##pivotos SELECT DISTINCT ' + @pivot + ' FROM ' + @table + ' WHERE '
+ @pivot + ' Is Not Null')
SELECT @sql='',
@sumfunc=stuff(@sumfunc, len(@sumfunc), 1, ' END)' )

--print(@sumfunc)

SELECT @delim=CASE Sign( CharIndex('char', data_type)+CharIndex('date', data_type) )
WHEN 0 THEN '' ELSE '''' END
FROM tempdb.information_schema.columns
WHERE table_name='##pivotos' AND column_name='pivot'

SELECT @sql=@sql + '''' + convert(varchar(100), pivot) + ''' = ' +
stuff(@sumfunc,charindex( '(', @sumfunc )+1, 0, ' CASE ' + @pivot + ' WHEN '
+ @delim + convert(varchar(100), pivot) + @delim + ' THEN ' ) + ', ' FROM ##pivotos

DROP TABLE ##pivotos

SELECT @sql=left(@sql, len(@sql)-1)
SELECT @select=stuff(@select, charindex(' FROM ', @select)+1, 0, ', ' + @sql + ' ')

EXEC (@select)

[larry2]

Try this store procedure..
...=stuff(@select, charindex(' FROM ', @select)+1, 0, ', ' + @sql + ' ')

EXEC (@select)

I get incorrect syntax near the keyword 'pivot', line 31, then 34

tried changing a few things (pivot to piv so not reserved word etc.) but sol.

Will take me a while to figure out what this routine is doing. I take it this is a generic procedure you use? Does it work as posted for you? I'm using MS SQL 2005