CODEWITHSUNDEEP
sql-servert-sqlsql-server-2012
TimJohnson
TimJohnson

Reputation: 933

Join columns to rows

Suppose you have a table Table1 with columns

UserId, Item1, Item2, Item3, Item4, Item5, Item6, Item7, Item8, Item9, Item10

and you have another table Table2 with

UserId, ItemId, Name

. The values in Table1 is the ItemId from Table2. I have a need to display

UserId, ItemId, Name

where Item1 is 1st and Item10 is last and you have 10 rows. In other words, Item1 is 1st row and Item10 is last row. If there's any way to avoid CASE WHEN that would be great. I may have more columns in the future and would hate to hardcode the 10 columns.

Upvotes: 0

Views: 100

Answers (4)

danfolkes
danfolkes

Reputation: 414

What I did to work around this was to use Python (or R) and use the melt function. There is also a pivot_table function in the dataframe.

So, you can have your columns be converted to rows. Then join those rows on the other table.

Reshaping and Pivot Tables

Upvotes: 0

Christian
Christian

Reputation: 359

Given you can bypass doing it entirely with SQL, I would highly recommend using e.g. R or Python to process transactions in a ML useable way. The tidyr package with the gather function does exactly what you want to do.

Another way is to crosstabulate. It´s absolutely fine deriving a solution with the SQL standard, but a lot of problems can be much easier done within R or Python.

Upvotes: 1

Carsten Massmann
Carsten Massmann

Reputation: 28196

A table1 with just 3 columns

userid, itemid, sequence

would be more conducive for your purposes. You would be required to convert your AzureML output from the single line 

Uid1, itm1,itm2,itm3,...,itm10

into 10 lines like

Uid1, itm1, 1
Uid1, itm2, 2
Uid1, itm3, 3
...
Uid1, itm10,10

Assuming you get the above output line as a (temporary) table output from AzureML with name tbla you could use the follwing UNION ALL construct (as suggested by Spencer Simpson):

INSERT INTO table1 (userid, itemid, sequence)
SELECT uid, itm1, 1 FROM tbla UNION ALL
SELECT uid, itm2, 2 FROM tbla UNION ALL
SELECT uid, itm3, 3 FROM tbla UNION ALL
SELECT uid, itm4, 4 FROM tbla UNION ALL
...
SELECT uid, itm10, 10 FROM tbla

To store the information into table1 which will be the only table you will have to deal with. No JOINs will be required anymore.

Note: I am not quite sure what your column name relates to. Is it the name of an item or the name of a user?

In both cases there should be a second table table2 that takes care of the correspondence between name and userid/itemid like

itm/usr   name

This table will then be join-ed into any query that requires displaying the name column too.

Upvotes: 0

SlimsGhost
SlimsGhost

Reputation: 2909

I think you want a reverse pivot in this case. You don't use CASE, like you would in a normal pivot, but instead UNION ALL, like this:

select Table1.UserId, Table2.ItemId, Table2.Name
from Table1 inner join Table2 on Table1.Item1 = Table2.ItemId
UNION ALL
select Table1.UserId, Table2.ItemId, Table2.Name
from Table1 inner join Table2 on Table1.Item2 = Table2.ItemId
UNION ALL
...
select Table1.UserId, Table2.ItemId, Table2.Name
from Table1 inner join Table2 on Table1.Item10 = Table2.ItemId

If you have more items, you should also be able to write a snippet that generates the repeating UNION ALL syntax so you don't have to type it all by hand.

Upvotes: 1

Related Questions