CODEWITHSUNDEEP
sqlsql-servert-sqldynamic-pivot
Diego
Diego

Reputation: 46

transposing a table

Hi I would like to rotate a full table:

LANGUAGE litAccept litDelete litErrorMsg  ..............
-------------------------------------------------
SPANISH  Accept    Delete    Error has ocurred
ENGLISH  Aceptar   Borrar    Ha ocurrido un error
.....

into something like this:

LANGUAGE     ENGLISH   SPANISH
---------------------------------------
litAccept    Accept    Aceptar    
litDelete    Aceptar   Borrar    
litErrorMsg  Error..   Ha ocurridO..
...

I see with PIVOT you can rotate a table, but I don't know who do this!

Please help, thanks

Upvotes: 2

Views: 288

Answers (3)

Taryn
Taryn

Reputation: 247620

This can be completed using Dynamic SQL with both an UNPIVOT and then a PIVOT. 

DECLARE @colsUnPivot AS NVARCHAR(MAX),
    @colsPivot AS NVARCHAR(MAX),
    @query  AS NVARCHAR(MAX)

SET @colsUnPivot = stuff((select ','+quotename(C.name)
         from sys.columns as C
         where C.object_id = object_id('t') and
               C.name like 'lit%'
         for xml path('')), 1, 1, '')

SET @colsPivot = STUFF((SELECT distinct ',' + QUOTENAME(c.Language) 
            FROM t c
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'')

set @query = 
            'SELECT * from 
            (
                select Language l1, type language, value
                from t
                unpivot
                (
                    value
                    for type in (' + @colsUnPivot + ')
                ) upvt
            ) x
            pivot 
            (
                min(value)
                for l1 in (' + @colsPivot + ')
            ) p '


execute(@query)

See SQL Fiddle with a Demo

Upvotes: 0

Diego
Diego

Reputation: 46

Wow I have done it!!

Now I understand how it works PIVOT. I tried to rotate the entire table at once with dynamic SQL and there is no way to fit it in the syntax of pivot. The rotation must be done column by column (PIVOT) or line by line (UNPIVOT) inserting them in a temporally table to get the full transposing at the end.

I think this is the way for me.. but maybe it can be done in single query?? (UNION ALL ins't valid, you know ;P)

Next day I will write the solution I found.

Thanks!

Upvotes: 1

adolf garlic
adolf garlic

Reputation: 3096

http://www.simple-talk.com/community/blogs/andras/archive/2007/09/14/37265.aspx

Pivot can't do 'dynamic' so you'll have to use this method

Upvotes: 1

Related Questions