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 — Thom A
– Thom A ♦, Commented Nov 20, 2020 at 10:56
@Larnu yes this is helpful but showing redundant data because of left join. — Maulik Dave
– Maulik Dave, Commented Nov 20, 2020 at 12:41
Then change to an INNER JOIN? What do you mean by "redundant"? — Thom A
– Thom A ♦, Commented Nov 20, 2020 at 12:46

Thom A · Accepted Answer · 2020-11-20 12:51:26Z

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, 2020 at 12:51

Thom A♦

97.7k12 gold badges67 silver badges102 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

Maulik Dave Over a year ago

This Works good but I am getting Same Record multiple times, can you help me with this. (I meant this by "Redundant" data)

Thom A Over a year ago

Not without sample data, @MaulikDave .

Maulik Dave Over a year ago

Here's the Sample Data, I think I can use DISTINCT But can not figure out where.

Thom A Over a year ago

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.

Maulik Dave Over a year ago

That Link is of The Snap shot of The sample Data but it's Okay.. I Will ask new question.

|

Collectives™ on Stack Overflow

Convert Column Data into Row in SQL Server

1 Answer 1

6 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

6 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related