CODEWITHSUNDEEP
sql-serversql-server-2008
Amber
Amber

Reputation: 113

use select result as column

I have a temp table with values inside

select distinct sub from #t

which gives me values

1-aaa
2-bbb
3-ccc
....
so on

and then I'm doing the following 

select fullname,
  max(case when sub='aaa' then score end) 'aaa',
  max(case when sub='bbb' then score end) 'bbb',
  max(case when sub='ccc' then score end) 'ccc'
from #t
group by fullname

and this gives a result of

fullname - aaa - bbb - ccc

fn1 - 15 - 18 - 21
fn2 - 18 - 23 - 33
fn3 - xx - yy - zz

the thing is that #t might contain up to 100's of "names"

here's what I can't do

is there a way to change the part

max(case when sub='aaa' then score end) 'aaa'

with for/each like loop based on valeus from #t?

thanks

Upvotes: 1

Views: 59

Answers (1)

Thom A
Thom A

Reputation: 95554

Speaking, however, on Dynamic SQL, here is a solution that does do this dynamically for you:

CREATE TABLE #T (FullName varchar(15),
                 sub char(3),
                 score int);
INSERT INTO #T
VALUES ('Joe Bloggs','aaa',17),
       ('Joe Bloggs','bbb',9),
       ('Joe Bloggs','ccc',24),
       ('Joe Bloggs','eee',14),
       ('Joe Bloggs','fff',12),
       ('Jane Smith','aaa',2),
       ('Jane Smith','ccc',12),
       ('Jane Smith','ddd',15),
       ('Jane Smith','eee',19),
       ('Jane Smith','ggg',21);

GO
DECLARE @SQL nvarchar(MAX);

SET @SQL = N'
SELECT Fullname,' + NCHAR(10) + STUFF((SELECT DISTINCT N',' + NCHAR(10) + N'MAX(CASE sub WHEN ''' + REPLACE(sub,N'''',N'''''') + N''' THEN score END) AS ' + QUOTENAME(sub) + N''
                                       FROM #T
                                       FOR XML PATH(N'')),1,2,N'') + '
FROM #T
GROUP BY Fullname;';

PRINT @SQL;
EXEC sp_executesql @SQL;

GO
DROP TABLE #t;

If you don't understand this, please do ask.

Upvotes: 1

Related Questions