SQL Server : Columns to Rows

Question

Looking for elegant (or any) solution to convert columns to rows.

Here is an example: I have a table with the following schema:

[ID] [EntityID] [Indicator1] [Indicator2] [Indicator3] ... [Indicator150]

Here is what I want to get as the result:

[ID] [EntityId] [IndicatorName] [IndicatorValue]

And the result values will be:

1 1 'Indicator1' 'Value of Indicator 1 for entity 1'
2 1 'Indicator2' 'Value of Indicator 2 for entity 1'
3 1 'Indicator3' 'Value of Indicator 3 for entity 1'
4 2 'Indicator1' 'Value of Indicator 1 for entity 2'

And so on..

Does this make sense? Do you have any suggestions on where to look and how to get it done in T-SQL?

Solution 1

You can use the UNPIVOT function to convert the columns into rows:

select id, entityId,
  indicatorname,
  indicatorvalue
from yourtable
unpivot
(
  indicatorvalue
  for indicatorname in (Indicator1, Indicator2, Indicator3)
) unpiv;

Note, the datatypes of the columns you are unpivoting must be the same so you might have to convert the datatypes prior to applying the unpivot.

You could also use CROSS APPLY with UNION ALL to convert the columns:

select id, entityid,
  indicatorname,
  indicatorvalue
from yourtable
cross apply
(
  select 'Indicator1', Indicator1 union all
  select 'Indicator2', Indicator2 union all
  select 'Indicator3', Indicator3 union all
  select 'Indicator4', Indicator4 
) c (indicatorname, indicatorvalue);

Depending on your version of SQL Server you could even use CROSS APPLY with the VALUES clause:

select id, entityid,
  indicatorname,
  indicatorvalue
from yourtable
cross apply
(
  values
  ('Indicator1', Indicator1),
  ('Indicator2', Indicator2),
  ('Indicator3', Indicator3),
  ('Indicator4', Indicator4)
) c (indicatorname, indicatorvalue);

Finally, if you have 150 columns to unpivot and you don't want to hard-code the entire query, then you could generate the sql statement using dynamic SQL:

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

select @colsUnpivot 
  = stuff((select ','+quotename(C.column_name)
           from information_schema.columns as C
           where C.table_name = 'yourtable' and
                 C.column_name like 'Indicator%'
           for xml path('')), 1, 1, '')

set @query 
  = 'select id, entityId,
        indicatorname,
        indicatorvalue
     from yourtable
     unpivot
     (
        indicatorvalue
        for indicatorname in ('+ @colsunpivot +')
     ) u'

exec sp_executesql @query;

Solution 2

well If you have 150 columns then I think that UNPIVOT is not an option. So you could use xml trick

;with CTE1 as (
    select ID, EntityID, (select t.* for xml raw('row'), type) as Data
    from temp1 as t
), CTE2 as (
    select
         C.id, C.EntityID,
         F.C.value('local-name(.)', 'nvarchar(128)') as IndicatorName,
         F.C.value('.', 'nvarchar(max)') as IndicatorValue
    from CTE1 as c
        outer apply c.Data.nodes('row/@*') as F(C)
)
select * from CTE2 where IndicatorName like 'Indicator%'

sql fiddle demo

You could also write dynamic SQL, but I like xml more - for dynamic SQL you have to have permissions to select data directly from table and that's not always an option.

UPDATE
As there a big flame in comments, I think I'll add some pros and cons of xml/dynamic SQL. I'll try to be as objective as I could and not mention elegantness and uglyness. If you got any other pros and cons, edit the answer or write in comments

cons

• it's not as fast as dynamic SQL, rough tests gave me that xml is about 2.5 times slower that dynamic (it was one query on ~250000 rows table, so this estimate is no way exact). You could compare it yourself if you want, here's sqlfiddle example, on 100000 rows it was 29s (xml) vs 14s (dynamic);
• may be it could be harder to understand for people not familiar with xpath;

pros

• it's the same scope as your other queries, and that could be very handy. A few examples come to mind
  • you could query inserted and deleted tables inside your trigger (not possible with dynamic at all);
  • user don't have to have permissions on direct select from table. What I mean is if you have stored procedures layer and user have permissions to run sp, but don't have permissions to query tables directly, you still could use this query inside stored procedure;
  • you could query table variable you have populated in your scope (to pass it inside the dynamic SQL you have to either make it temporary table instead or create type and pass it as a parameter into dynamic SQL;
• you can do this query inside the function (scalar or table-valued). It's not possible to use dynamic SQL inside the functions;

Solution 3

Just to help new readers, I've created an example to better understand @bluefeet's answer about UNPIVOT.

 SELECT id
        ,entityId
        ,indicatorname
        ,indicatorvalue
  FROM (VALUES
        (1, 1, 'Value of Indicator 1 for entity 1', 'Value of Indicator 2 for entity 1', 'Value of Indicator 3 for entity 1'),
        (2, 1, 'Value of Indicator 1 for entity 2', 'Value of Indicator 2 for entity 2', 'Value of Indicator 3 for entity 2'),
        (3, 1, 'Value of Indicator 1 for entity 3', 'Value of Indicator 2 for entity 3', 'Value of Indicator 3 for entity 3'),
        (4, 2, 'Value of Indicator 1 for entity 4', 'Value of Indicator 2 for entity 4', 'Value of Indicator 3 for entity 4')
       ) AS Category(ID, EntityId, Indicator1, Indicator2, Indicator3)
UNPIVOT
(
    indicatorvalue
    FOR indicatorname IN (Indicator1, Indicator2, Indicator3)
) UNPIV;

Solution 4

Just because I did not see it mentioned.

If 2016+, here is yet another option to dynamically unpivot data without actually using Dynamic SQL.

Example

Declare @YourTable Table ([ID] varchar(50),[Col1] varchar(50),[Col2] varchar(50))
Insert Into @YourTable Values 
 (1,'A','B')
,(2,'R','C')
,(3,'X','D')

Select A.[ID]
      ,Item  = B.[Key]
      ,Value = B.[Value]
 From  @YourTable A
 Cross Apply ( Select * 
                From  OpenJson((Select A.* For JSON Path,Without_Array_Wrapper )) 
                Where [Key] not in ('ID','Other','Columns','ToExclude')
             ) B

Returns

ID  Item    Value
1   Col1    A
1   Col2    B
2   Col1    R
2   Col2    C
3   Col1    X
3   Col2    D

Solution 5

I needed a solution to convert columns to rows in Microsoft SQL Server, without knowing the colum names (used in trigger) and without dynamic sql (dynamic sql is too slow for use in a trigger).

I finally found this solution, which works fine:

SELECT
    insRowTbl.PK,
    insRowTbl.Username,
    attr.insRow.value('local-name(.)', 'nvarchar(128)') as FieldName,
    attr.insRow.value('.', 'nvarchar(max)') as FieldValue 
FROM ( Select      
          i.ID as PK,
          i.LastModifiedBy as Username,
          convert(xml, (select i.* for xml raw)) as insRowCol
       FROM inserted as i
     ) as insRowTbl
CROSS APPLY insRowTbl.insRowCol.nodes('/row/@*') as attr(insRow)

As you can see, I convert the row into XML (Subquery select i,* for xml raw, this converts all columns into one xml column)

Then I CROSS APPLY a function to each XML attribute of this column, so that I get one row per attribute.

Overall, this converts columns into rows, without knowing the column names and without using dynamic sql. It is fast enough for my purpose.

(Edit: I just saw Roman Pekar answer above, who is doing the same. I used the dynamic sql trigger with cursors first, which was 10 to 100 times slower than this solution, but maybe it was caused by the cursor, not by the dynamic sql. Anyway, this solution is very simple an universal, so its definitively an option).

I am leaving this comment at this place, because I want to reference this explanation in my post about the full audit trigger, that you can find here: https://stackoverflow.com/a/43800286/4160788

View more solutions

Comments

[Josh Jay]

Have you looked into Pivot/Unpivot yet?

[Sergei]

At the end of it went with the bluefeet's solution. Elegant and functional. Thanks a lot everyone.

[Adir D]

What data are you selecting with XML that doesn't require selecting data from the table?

[Roman Pekar]

For example you could decide not to give users permissions to select data from tables, but only on stored procedures working with tables, so I could select for xml inside the procedure, but I have to use some workarounds if I want to use dynamic SQL

[Adir D]

If you want your users to be able to execute the code, you kind of have to give them whatever access they need to execute the code. Don't make up requirements that don't exist to make your answer sound better (you also don't have to comment on competing answers to look at your answer - if they found that answer, they can find yours too).

[Roman Pekar]

I could give them permission to stored procedure and if stored procedure working with tables from same db, users don't need permissions to select from table, do you know that? Is it looking strange for you not to give all users rights to select all data they want from table? For example, I have some horizontal autorization system, so I don't want users to be able to fetch all rows

[Adir D]

Did you know that you can use a view to expose only certain columns (or rows!) to users, and they could write a more straightforward query against that view?

[Roman Pekar]

@AaronBertrand it's real system I told you about, don't think that I need to make up something to make my answer sound better

[Adir D]

My point was that it is not currently known to be one of the OP's requirements, so it's hard to use that to justify why your answer is better.

[Roman Pekar]

@AaronBertrand yes I do, and sometimes I need to pass parameters to procedure so view not always an option.

[Roman Pekar]

@AaronBertrand have I said somewhere that my answer is better? I think it's elegant solution and I said that I like it more.

[Adir D]

Huh? Your stored procedure can accept parameters, then you can use them against the view. You can also create a table-valued function that acts like a parameterized view. You don't have to solve security issues by writing obscure XML.

[Adir D]

So you like it more than the other answer, but it's not better? Do you have any clue how community works? sigh

[Roman Pekar]

@AaronBertrand I like it more is suubjective, better is objective

[Adir D]

Also if your justification for using XML is that you can put it in a stored procedure to avoid giving direct access to the table, maybe your example should show how to put it into a stored procedure and how to grant rights to a user so that they can execute it without having read access to the underlying table. To me that's scope creep, because most people writing queries against a table have read access to the table.

[Adir D]

And please be aware that when you are answering questions that other people are supposed to learn from, the opinions that you state are always going to sound more objective and matter-of-fact than you think. "I like this better" is often going to translate to "This is better."

[Roman Pekar]

for me, dynamic sql looks uglier than this xml solution, it's basically simple select, I can put data into table (without using exec into or shared table). I thought that I would point one of the reasons I met in my expirience why somebody could prefer that way. OP is free to choose any solution he wants. I thought about putting some other but then this "discussion" arise

[Roman Pekar]

I don't know about you, but for me this solution is quite elegant - just one seleect - and you have EAV data :)

[Adir D]

Yeah, now explain to a total newbie how it works, and see how easily they can adapt this query to a different scenario.

[Adir D]

You might want to add performance somewhere higher on your priority list than security requirements that don't even exist. You say that dynamic SQL is uglier than the XML; I say that ugly is less important in the code if it works like this.

[Roman Pekar]

yes it could be slower on big amounts of data but for me it generalize better then dynamic SQL and sometimes it matters too. I mean - C++ is faster than python but sometimes you have to write generalizable code :)

[Adir D]

I'd say a 10x difference in duration does matter, yes. And ~8,000 rows is not "big amounts of data" - should we see what happens against 800,000 rows?

[Roman Pekar]

well I don't know how have you tested it, it's not always about plan, but about actual execution. I've tried it on ~250000 rows: my query about 2.5x (not 10) times slower on my tablet (20s vs 49s). But then I've tried to put data into temporary table (plain insert for my query, insert into exec for dynamic sql, and my query worked for ~35s while insert into exec worked about 1.5min and then I've stopped it. Honestly, when I moved insert into temp table into dynamic, it worked for ~15s. If you don't do it all the time for big number of rows, i think it don't matter that much

[Roman Pekar]

if not for perf. critical tasks and I doubt that one is

[Adir D]

@Roman I'm not sure why you get to assume that this user's problem is not "perf. critical" but, more importantly, remember that this answer will be read by many other users in the future, too. So presuming that none of their problems will be "perf. critical" either is pretty dangerous. I'm glad you acknowledged the performance issues in the post (even though you've downplayed them), but I think they're still more important than you're admitting.

[ruffin]

For those who want more nuts and bolts about UNPIVOT and/vs. APPLY, this 2010 blog post from Brad Schulz (and the follow-on) is (are) beautiful.

[JDPeckham]

Msg 8167, Level 16, State 1, Line 147 The type of column "blahblah" conflicts with the type of other columns specified in the UNPIVOT list.

[Taryn]

@JDPeckham If you have different datatypes, then you need to convert them to be the same type and length prior to performing the unpivot. Here is more information about that.

[BI Dude]

What does F(C) actually do?

[Roman Pekar]

It´s aliasing the recordset - F is alias for table and C is alias for column

[rrozema]

the xml method has a flaw because it fails to unescape xml codes like >, < and &. Plus the performance can be significantly improved by rewriting as follows: select @colsUnpivot = stuff((select ','+quotename(C.column_name) as [text()] from information_schema.columns as C where C.table_name = 'yourtable' and C.column_name like 'Indicator%' for xml path(''), type).value('text()[1]','nvarchar(max)'), 1, 1, '')

[user8124226]

Thank for your help

[Mohsen]

I really loved this post. Thank you @Taryn.