Convert Column Data into Row in SQL Server

Question

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
+------+----------+-------------+--------+

Does this answer your question? [SQL Server : Columns to Rows](https://stackoverflow.com/q/18026236/2029983) — Thom A, Nov 20 '20 at 10:56
@Larnu yes this is helpful but showing redundant data because of left join. — Dave, Nov 20 '20 at 12:41
Then change to an `INNER JOIN`? What do you mean by "redundant"? — Thom A, Nov 20 '20 at 12:46

score 1 · Accepted Answer · answered Nov 20 '20 at 12:51

1

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]);

answered Nov 20 '20 at 12:51

Thom A

88,727
11
45
75

This Works good but I am getting Same Record multiple times, can you help me with this. (I meant this by "Redundant" data) – Dave Nov 20 '20 at 13:14
Not without sample data, @MaulikDave . – Thom A Nov 20 '20 at 13:16
[Here's](https://www.screencast.com/t/GsvEcQXPChvt) the Sample Data, I think I can use `DISTINCT` But can not figure out where. – Dave Nov 20 '20 at 13:22
I don't know what that link is, but I'm not clicking it, @MaulikDave . You're better off asking a new question with realistic sample data at this stage, as there are no duplicates in your one example. – Thom A Nov 20 '20 at 13:23
That Link is of The Snap shot of The sample Data but it's Okay.. I Will ask new question. – Dave Nov 20 '20 at 13:26
As I thought I used `DISTINCT *` and it Worked – Dave Nov 20 '20 at 14:26

Convert Column Data into Row in SQL Server

1 Answers1