CODEWITHSUNDEEP
sqlt-sqlunpivotsql-server-2014
Shashank
Shashank

Reputation: 107

Unpivoting multiple columns

I have a table in SQL Server 2014 called anotes with the following data

enter image description here

and I want to add this data into another table named final as

ID      Notes      NoteDate

With text1, text2, text3, text4 going into the Notes column in the final table and Notedate1,notedate2,notedate3,notedate4 going into Notedate column.

I tried unpivoting the data with notes first as:

select createdid, temp
from (select createdid,text1,text2,text3,text4 from anotes) p
unpivot
(temp for note in(text1,text2,text3,text4)) as unpvt
order by createdid

Which gave me proper results:

enter image description here

and then for the dates part I used another unpivot query:

select createdid,temp2
from (select createdid,notedate1,notedate2,notedate3,notedate4 from anotes) p
unpivot (temp2 for notedate in(notedate1,notedate2,notedate3,notedate4)) as unpvt2

which also gives me proper results:

enter image description here

Now I want to add this data into my final table.

and I tried the following query and it results into a cross join :(

select a.createdid, a.temp, b.temp2
from (select createdid, temp
      from (select createdid,text1,text2,text3,text4 from anotes) p
      unpivot
      (temp for note in(text1,text2,text3,text4)) as unpvt) a inner join (select createdid,temp2
from (select createdid,notedate1,notedate2,notedate3,notedate4 from anotes) p
unpivot (temp2 for notedate in(notedate1,notedate2,notedate3,notedate4)) as unpvt) b on a.createdid=b.createdid

The output is as follows:

enter image description here

Is there any way where I can unpivot both the columns at the same time?

Or use two select queries to add that data into my final table?

Thanks in advance!

Upvotes: 1

Views: 359

Answers (3)

GarethD
GarethD

Reputation: 69759

I would say the most concise, and probably most efficient way to unpivot multiple columns is to use CROSS APPLY along with a table valued constructor:

SELECT  t.CreatedID, upvt.Text, upvt.NoteDate
FROM    anotes t
        CROSS APPLY
        (VALUES
            (Text1, NoteDate1),
            (Text2, NoteDate2),
            (Text3, NoteDate3),
            (Text4, NoteDate4),
            (Text5, NoteDate5),
            (Text6, NoteDate6),
            (Text7, NoteDate7)
        ) upvt (Text, NoteDate);

Simplified Example on SQL Fiddle

ADDENDUM

I find the concept quite a hard one to explain, but I'll try. A table valued constuctor is simply a way of defining a table on the fly, so 

SELECT  *
FROM    (VALUES (1, 1), (2, 2)) t (a, b);

Will Create a table with Alias t with data:

a   b
------
1   1
2   2

So when you use it inside the APPLY you have access to all the outer columns, so it is just a matter of defining your constructed tables with the correct pairs of values (i.e. text1 with date1).

Upvotes: 3

Jerrad
Jerrad

Reputation: 5290

Treat each query as a table and join them together based on the createdid and the fieldid (the numeric part of the field name).

select x.createdid, x.textValue, y.dateValue
from
(
    select createdid, substring(note, 5, len(note)) fieldId, textValue
    from (select createdid,text1,text2,text3,text4 from anotes) p
    unpivot
    (textValue for note in(text1,text2,text3,text4)) as unpvt
)x
join
(
    select createdid, substring(notedate, 9, len(notedate)) fieldId, dateValue
    from (select createdid,notedate1,notedate2,notedate3,notedate4 from anotes) p
    unpivot (dateValue for notedate in(notedate1,notedate2,notedate3,notedate4)) as unpvt2
) y on x.fieldId = y.fieldId and x.createdid = y.createdid
order by x.createdid, x.fieldId

The other answer given won't work if you have too many columns and the rightmost number of the field name is duplicated (e.g. text1 and text11).

Upvotes: 0

Shashank
Shashank

Reputation: 107

Used the link above mentioned by @AHiggins

Following is my final query!

select createdid,temp,temp2
from (select createdid,text1,text2,text3,text4,text5,text6,text7,notedate1,notedate2,notedate3,notedate4,notedate5,notedate6,notedate7 from anotes) main
      unpivot
      (temp for notes in(text1,text2,text3,text4,text5,text6,text7)) notes
unpivot (temp2 for notedate in(notedate1,notedate2,notedate3,notedate4,notedate5,notedate6,notedate7)) Dates
where RIGHT(notes,1)=RIGHT(notedate,1)

Upvotes: 0

Related Questions