transpose of table dynamically different data types

Question

I found this great post for transposing a table in sql:

Simple way to transpose columns and rows in Sql?

edit:

input:
        Paul     | John  | Tim  |  Eric
Red    'hi'      |   5   |    1 |   3.3
Green  'there'   |   4   |    3 |   5.5
Blue   'everyone'|   2   |    9 |   7.5

expected output:

       Red   |  Green   | Blue
Paul   'hi'  | 'there'  | 'everyone'
John    5    |   4      |   2
Tim     1    |   3      |   9
Eric    3.3  |   5.5    |   7.5

And I wanted to employ the last dynamic solution for a table that has different data types dynamically:

CREATE TABLE yourTable([color] nvarchar(5), [Paul] nvarchar(10), [John] int, [Tim] 
int, [Eric] float);

INSERT INTO yourTable
([color], [Paul], [John], [Tim], [Eric])
VALUES
('Red', 'hi', 5, 1, 3.3),
('Green', 'there', 4, 3, 5.5),
('Blue', 'everyone', 2, 9, 7.5);

When I run the code from the previous answer:

DECLARE @colsUnpivot AS NVARCHAR(MAX),
@query  AS NVARCHAR(MAX),
@colsPivot as  NVARCHAR(MAX)

select @colsUnpivot = stuff((select ','+quotename(C.name)
     from sys.columns as C
     where C.object_id = object_id('yourtable') and
           C.name <> 'color'
     for xml path('')), 1, 1, '')

select @colsPivot = STUFF((SELECT  ',' 
                  + quotename(color)
                from yourtable t
        FOR XML PATH(''), TYPE
        ).value('.', 'NVARCHAR(MAX)') 
    ,1,1,'')


set @query 
= 'select name, '+@colsPivot+'
  from
  (
    select color, name, value
    from yourtable
    unpivot
    (
      value for name in ('+@colsUnpivot+')
    ) unpiv
  ) src
  pivot
  (
    sum(value)
    for color in ('+@colsPivot+')
  ) piv'

exec(@query)

When I run this code I get the error message:

The type of column "John" conflicts with the type of other columns specified in the UNPIVOT list.

Is there a way that I can use this dynamic solution for my table without losing the dynamic nature of it? I'd like to ideally pass a bunch of tables into this method to transpose them in batch.

Thanks

What is your expected output? – Kannan Kandasamy Jun 10 '17 at 00:28 — Kannan Kandasamy, Jun 10 '17 at 00:28
added input/output to post. thanks – Zee Fer Jun 10 '17 at 00:45 — Zee Fer, Jun 10 '17 at 00:45

Paul Maxwell · Accepted Answer · 2017-06-12T22:09:26.643

A method to overcome this would be to use the data type SQL_VARIANT so that the resulting columns can handle more than a single data type. However you cannot SUM() SQL_VARIANT columns, so sum(value) has to be changed to max(value) - or min(value) - but for this pivot that change does not alter the result.

DECLARE @colsConvert AS NVARCHAR(MAX)
DECLARE @colsUnpivot AS NVARCHAR(MAX)
DECLARE @colsPivot as  NVARCHAR(MAX)
DECLARE @query  AS NVARCHAR(MAX)


select @colsConvert = (select ', cast('+quotename(C.name)+' as sql_variant) as '+quotename(C.name)
     from sys.columns as C
     where C.object_id = object_id('yourtable') and
           C.name <> 'color'
     for xml path(''))
     
select @colsUnpivot = stuff((select ','+quotename(C.name)
     from sys.columns as C
     where C.object_id = object_id('yourtable') and
           C.name <> 'color'
     for xml path('')), 1, 1, '')

select @colsPivot = STUFF((SELECT  ',' 
                  + quotename(color)
                from yourtable t
        FOR XML PATH(''), TYPE
        ).value('.', 'NVARCHAR(MAX)') 
    ,1,1,'')


set @query 
= 'select name, '+@colsPivot+'
  from
  (
    select color, name, value
    from (select color'+@colsConvert+' from yourtable) as converted
    unpivot
    (
      value for name in ('+@colsUnpivot+')
    ) unpiv
  ) src
  pivot
  (
    max(value)
    for color in ('+@colsPivot+')
  ) piv'

exec(@query)

See this working at: http://rextester.com/IBSN39688

Result:

+------+-----+-------+----------+
| name | Red | Green |   Blue   |
+------+-----+-------+----------+
| Eric | 3.3 | 5.5   | 7.5      |
| John | 5   | 4     | 2        |
| Paul | hi  | there | everyone |
| Tim  | 1   | 3     | 9        |
+------+-----+-------+----------+

The generated SQL:

select name, [Red],[Green],[Blue]
  from
  (
    select color, name, value
    from (select color, cast([Eric] as sql_variant) as [Eric], cast([John] as sql_variant) as [John], cast([Paul] as sql_variant) as [Paul], cast([Tim] as sql_variant) as [Tim] from yourtable) as converted
    unpivot
    (
      value for name in ([Eric],[John],[Paul],[Tim])
    ) unpiv
  ) src
  pivot
  (
    max(value)
    for color in ([Red],[Green],[Blue])
  ) piv

+EDIT

An added benefit of using SQL_VARIANT in the result columns is that each standard data type encountered will adopt its default format. Particularly relevant for decimal/float and date/time data. You could also amend the defaults before running the dynamic pivot to further influence the output.

Demonstrated here

score 0 · Answer 2 · answered Jun 10 '17 at 13:37

The following will transpose virtually any table, view, or query while respecting Row and Column sequences.

Full Disclosure: There is one major drawback. This approach does NOT handle NULL values well. A NULL value will cause the following columns to shift to the left.

Example

Declare @YourTable Table ([Color] varchar(50),[Paul] varchar(50),[John] int,[Tim] int,[Eric] decimal(10,1))
Insert Into @YourTable Values
 ('Red','hi',5,1,3.3)
,('Green','there',4,3,5.5)
,('Blue','everyone',2,9,7.5)

Declare @XML xml = (Select *,RowNr=Row_Number() over (Order By (Select NULL)) From  @YourTable for XML RAW)

Select RowNr = Row_Number() over(Partition By r.value('@RowNr','int') Order By (Select null))
      ,ColNr = r.value('@RowNr','int')
      ,Item  = attr.value('local-name(.)','varchar(100)')
      ,Value = attr.value('.','varchar(max)') 
 Into  #Temp
 From  @XML.nodes('/row') as XN(r)
 Cross Apply XN.r.nodes('./@*') AS XA(attr)
 Where attr.value('local-name(.)','varchar(100)') not in ('RowNr')

Declare @SQL varchar(max) = '
Select [Item],' + Stuff((Select Distinct ',' + QuoteName(ColNr)+' as '+QuoteName(Value) From #Temp Where RowNr=1 Order by 1 For XML Path('')),1,1,'') + '
 From  #Temp 
 Pivot (max([Value]) For [ColNr] in (' + Stuff((Select Distinct ',' + QuoteName(ColNr) From #Temp Order by 1 For XML Path('')),1,1,'') + ') ) p 
 Where RowNr>1
 Order By RowNr'
Exec(@SQL);

Returns

Item    Red   Green   Blue
Paul    hi    there   everyone
John    5     4       2
Tim     1     3       9
Eric    3.3   5.5     7.5

dbFiddle

transpose of table dynamically different data types

2 Answers2