CODEWITHSUNDEEP
sql-serverstored-procedures
Dave
Dave

Reputation: 166

Convert Column Data into Row in SQL Server

I have following Store Procedure.

Declare @myData TABLE( 
                        Id int,
                        Note1 nvarchar(Max),
                        Note2 nvarchar(Max),
                        Note3 nvarchar(Max),
                    )
Insert into @myData (Id,Note1,Note2,Note3)
Select
--First Column
E.Id as [ID],
E.Notes as [Note1],
E.NotesDate as [Date1]

F.ItemDescription as [Note2],
F.DescriptionDate as [Date2]

G.Description as [Note3],
G.DescDate as [Date3]

From 
Employee E
LEFT Join Tab1 as F
    on I.EmpId = E.Id
LEFT join Tab2 AS G 
    on G.EmpId = E.Id
where E.Id = 1019
select * from @myData

It gives Following result table.

+------+----------+------------+-------+-------------+---------+--------------+
| ID   | Note1    | Date1      | Note2 |  Date2      |  Note3   | Date3       |
+------+----------+------------+-------+-------------+----------+-------------+
|    1 | admils   | 2020-07-02 |  sadd |  2020-06-08 |  1230    | 2020-05-06  |
+------+----------+--------+---------+---------------+----------+-------------+

Now i need resulting table some thing like below...

+------+----------+-------------+--------+
| ID   | Note1    | Date        | Col    |
+------+----------+-------------+--------+
|    1 | admils   | 2020-07-02  |    1   | <- Because This Data is from Note1 Column
|    1 | sadd     | 2020-06-08  |    2   | <- Because This Data is from Note2 Column
|    1 | 1230     | 2020-05-06  |    3   | <- Because This Data is from Note3 Column
+------+----------+-------------+--------+

Upvotes: 0

Views: 42

Answers (1)

Thom A
Thom A

Reputation: 95554

Just unpivot your data, as shown in the linked duplicate candidate. I prefer using VALUES over UNPIVOT as it's less restrictive:

SELECT E.ID,
       V.[Description],
       V.[Date],
       V.Col
FROM dbo.Employee E
     LEFT JOIN dbo.Tab1 T1 ON E.ID = T1.EmpID
     LEFT JOIN dbo.Tab2 T2 ON E.ID = T2.EmpID
     CROSS APPLY (VALUES(1,E.NotesDate,E.Notes),
                        (2,T1.DescriptionDate,T1.ItemDescription),
                        (3,T2.DescDate,T2.Description))V(Col,[Date],[Description]);

Upvotes: 1

Related Questions