How can I convert columns data into row data sql?

Question

I have a table like so, which stores classes like this. There are probably 30 classes that are listed this way. Some examples of class names are 'Blood Borne Safety', 'CPR Training', 'Fork Lift'.

Name  |    Class1     |  Class1TrainerName  |  Class2   | Class2TrainerName 
------|---------------|---------------------|-----------|---------------
Scott |    3/28/2017  |  Casey              | 4/19/2017 |  David
Jim   |               |                     | 2/9/2019  |  David

And I want to turn it to look like this.

Name    |   ClassName  |  ClassDate  |  Trainer | 
--------|--------------|-------------|----------|
Scott   |   Class1     |  3/28/2017  | Casey    |
Scott   |   Class2     |  4/19/2017  | David    |
Jim     |   Class2     |  2/9/2019   | David    |

How can I convert table1 to data for table2?

@DaleBurrell thanks for you comment. My problem is that I do not even know where to start? How can I turn headers into data? — thalacker, Apr 15 '19 at 20:29
I just hope your task is to fix this table design. That is ugly. Addming complexity here is that you can't even use a standard UNPIVOT because for some columns the column name is the data and in other columns it is still the column name. — Sean Lange, Apr 15 '19 at 20:41
@SeanLange, yes I tried to do the unpivot method but couldn't figure it out due to the number of different column names. Also, I agree that it is ugly. That is why I am trying to fix it :) — thalacker, Apr 15 '19 at 20:43
Research is part of programmer's job. Have you tried Googling "converting columns to rows". Pretty sure there are tons of example. — Eric, Apr 15 '19 at 20:45
Does this have to be fully dynamic or do you know the number of columns you are dealing with? A statis solution here is not going to be simple, a full dynamic one is going to be downright ugly. — Sean Lange, Apr 15 '19 at 20:46
@Eric yes I did. I found many mentions of unpivot but couldn't get it to work with my sql table. https://stackoverflow.com/questions/18026236/sql-server-columns-to-rows was the most popular article. — thalacker, Apr 15 '19 at 20:46
@SeanLange I know the number of columns I am dealing with. I know that each one has a ClassName and a TrainerName — thalacker, Apr 15 '19 at 20:47
"I could'n't get it to work". Where's the query that doesn't work? I don't see it on your question. — Eric, Apr 15 '19 at 20:49

score 2 · Answer 1 · answered Apr 15 '19 at 20:56

This is a method explained in detail on this article.

It uses the advantage of a table value constructor to generate multiple rows from a single one. It also uses the APPLY operator to do it in a single read. Note that the first column can contain any string that will become your Class Name. The other 2 columns are the ones that are unpivoted.

SELECT mt.[Name],
    up.ClassName, 
    up.ClassDate, 
    up.Trainer
FROM MyTable mt
CROSS APPLY (VALUES('Class1', Class1, Class1TrainerName),
                   ('Class2', Class2, Class2TrainerName),
                   ('Class3', Class3, Class3TrainerName)) AS up(ClassName, ClassDate, Trainer);

The only thing I would add is a where clause like this `where up.ClassDate is not null AND up.ClassDate <> ''` — thalacker, Apr 15 '19 at 21:12

score 1 · Accepted Answer · answered Apr 15 '19 at 21:14

Just another option that will "dynamically" unpivot your data WITHOUT actually using dynamic SQL

Certainly, Luis's solution would be more performant, but here you don't have to detail all the column names and data types.

Example

Declare @YourTable Table ([Name] varchar(50),[Class1] date,[Class1TrainerName] varchar(50),[Class2] date,[Class2TrainerName] varchar(50))
Insert Into @YourTable Values 
 ('Scott','3/28/2017','Casey','4/19/2017','David')
,('Jim','2/9/2019','David',null,null)


;with cte as (
    Select RN
          ,A.Name
          ,C.*
          ,Grp = sum(case when Item not like '%TrainerName' then 1 end) over (Partition by RN Order by Seq)
     From  (Select *,RN = Row_Number() over (Order by (Select null)) From @YourTable) A
     Cross Apply ( values (cast((Select A.* for XML RAW) as xml))) B(XMLData)
     Cross Apply (
                    Select Item  = xAttr.value('local-name(.)', 'varchar(100)')
                          ,Value = xAttr.value('.','varchar(max)')
                          ,Seq   = Row_Number() over (Order by (Select null))
                     From  XMLData.nodes('//@*') xNode(xAttr)
                     Where xAttr.value('local-name(.)','varchar(100)') not in ('Name','RN')
                 ) C
) 
Select Name
      ,ClassName  = max(case when Item not like '%TrainerName' then Item end)
      ,ClassDate  = max(case when Item not like '%TrainerName' then Value end)
      ,Trainner   = max(case when Item like     '%TrainerName' then Value end)
 From cte
 Group By RN,Name,Grp

Returns

Name    ClassName   ClassDate   Trainner
Scott   Class1      2017-03-28  Casey
Scott   Class2      2017-04-19  David
Jim     Class1      2019-02-09  David

Amazing answer. This was so helpful and made it even easier. I know understand it much better — thalacker, Apr 15 '19 at 21:24
As an addition, if someone is looking at this and has a lot of special character in their column headers ("ClassName" for me) then you need to replace `Select Item = xAttr.value('local-name(.)', 'varchar(100)')` with `Select Item = Replace(Replace(Replace(Replace(Replace(Replace(Replace(xAttr.value('local-name(.)', 'varchar(200)'), '_x0020_', ' '), '_x0028_', '('), '_x0029_', ')'), '_x002C_', ','), '_x0026_', '&'), '_x0027_', ''''), '_x002F_', '/')` — thalacker, Apr 16 '19 at 13:58
@thalacker Ah, you have special characters... There may be an alternative. Give me a bit to experiment. — John Cappelletti, Apr 16 '19 at 14:02
yes I do, perhaps I should have mentioned that. However, once I did a replace like I mentioned above, it all worked out! Also, I had to run a quick procedure to make sure that all of my blank values where 'null' rather than ' '. — thalacker, Apr 16 '19 at 14:14
@thalacker Well done then ! Regarding the blank values ... perhaps a simple WHERE in the final select may help. ... WHERE C.Value<>'' Always fun to figure things out :) — John Cappelletti, Apr 16 '19 at 14:26

How can I convert columns data into row data sql?

2 Answers2