CODEWITHSUNDEEP
sqlsql-servercrosstab
Niq6
Niq6

Reputation: 7

Recreate Crosstab in SQL Server

I have a table that's like this:

FILENO  CODE    FIELD
FILE1   *CODE1  SOK
FILE1   *CODE2  AUTO
FILE1   *CODE3  CTY
FILE2   *CODE1  SOK
FILE2   *CODE2  AR
FILE2   *CODE3  CTN
FILE3   *CODE1  SOKN
FILE3   *CODE2  AUTO
FILE3   *CODE3  CTN

That I'd like to recreate in a view or new table like this:

FILENO  *CODE1  *CODE2  *CODE3
FILE1   SOK     AUTO    CTY
FILE2   SOK     AR      CTN    
FILE3   SOKN    AUTO    CTN

Essentially a cross-tab query but i'm not sure how to accomplish this in SSMS.

Any pointers?

Upvotes: 0

Views: 40

Answers (2)

John Cappelletti
John Cappelletti

Reputation: 82010

This is a very basic PIVOT. 

Select [FILENO],[*CODE1],[*CODE2],[*CODE3]
From   (Select FileNo,Code,Field from YourTable) A
Pivot (max(FIELD) For CODE in ([*CODE1],[*CODE2],[*CODE3]) ) p

Returns

FILENO  *CODE1  *CODE2  *CODE3
FILE1   SOK     AUTO    CTY
FILE2   SOK     AR      CTN
FILE3   SOKN    AUTO    CTN

For a Dynamic version

Declare @SQL varchar(max) = Stuff((Select Distinct ',' + QuoteName([Code]) From YourTable Order by 1 For XML Path('')),1,1,'') 
Select  @SQL = '
Select [FILENO],' + @SQL + '
From (Select FileNo,Code,Field from YourTable) A
Pivot (max(FIELD) For [CODE] in (' + @SQL + ') ) p'
Exec(@SQL);

Upvotes: 1

Ullas
Ullas

Reputation: 11556

You can achieve with a CASE expression.

Query

SELECT [FILENO],
MAX(CASE CODE WHEN '*CODE1' THEN FIELD END) AS [*CODE1],
MAX(CASE CODE WHEN '*CODE2' THEN FIELD END) AS [*CODE2],
MAX(CASE CODE WHEN '*CODE3' THEN FIELD END) AS [*CODE3]
FROM [your_table_name]
GROUP BY [FILENO];

Or you can use a dynamic sql query for more efficiency.

DECLARE @sql AS VARCHAR(MAX);
SELECT @sql = 'SELECT [FILENO], ' 
                + STUFF((SELECT DISTINCT  ', MAX(CASE [CODE] 
                WHEN ''' + [CODE] + ''' 
                THEN [FIELD] END) AS [' + [CODE] + ']'
                FROM [your_table_name]
                FOR XML PATH('')
                ), 1, 2, '')
                +' FROM [your_table_name] GROUP BY [FIELDNO]';

EXEC(@sql);

Upvotes: 0

Related Questions