CODEWITHSUNDEEP
sqlsql-server
GabrielVa
GabrielVa

Reputation: 2388

Joining several sql columns into one

I'm trying to combine some numbers and items into basically two columns. I have a table that has 7 Item columns and 7 qty columns along with a date and id field.

enter image description here

How can I combine them all into basically a couple of columns, even if the items repeat? Im trying to do something like this: Item1 QTY DATE With Item 1 perhaps repeating as many times as its in the columns.

The outcome needs to be just one column for the items and one for the qty along with the Date column next to it if possible.

Here is my current select statement

SELECT TOP (100) PERCENT 
    Item1, Qty1, Item2, Qty2, Item3, Qty3, Item4, Qty4, Item5, Qty5, 
    Item6, Qty6, Item7, Qty7, Date, ID
FROM         
    dbo.ITEMREPORT1

Upvotes: 0

Views: 124

Answers (2)

mikey
mikey

Reputation: 5160

You want to UNPIVOT. Why must every answer be spoon fed nowadays?

select Item, Quantity from
(
  select * from [dbo].[ITEMREPORT1]
  unpivot (
    Item for ItemOriginalColumn in (Item1, Item2, Item3, Item4, Item5, Item6, Item7)
  ) b
  unpivot (
    Quantity for QuantityOriginalColumn in (Qty1, Qty2, Qty3, Qty4, Qty5, Qty6, Qty7)
  ) c
) as d
where 
RIGHT(ItemOriginalColumn,1) = RIGHT(QuantityOriginalColumn, 1)

For what its worth, I used the very link from my answer that was downvoted/deleted to arrive at this solution... Enjoy.

Upvotes: 3

Taryn
Taryn

Reputation: 247700

In order to get the multiple columns into multiple rows, you will want to unpivot the data. If you are using SQL Server 2008+ you could implement CROSS APPLY with VALUES:

select c.item, c.qty, i.date
from dbo.ITEMREPORT1 i
cross apply
(
    values
    (Item1, Qty1),
    (Item2, Qty2),
    (Item3, Qty3),
    (Item4, Qty4),
    (Item5, Qty5),
    (Item6, Qty6),
    (Item7, Qty7)
) c (Item, Qty);

Or you can use CROSS APPLY with SELECT/UNION ALL:

select c.item, c.qty, i.date
from dbo.ITEMREPORT1 i
cross apply
(
    select Item1, Qty1 union all
    select Item2, Qty2 union all
    select Item3, Qty3 union all
    select Item4, Qty4 union all
    select Item5, Qty5 union all
    select Item6, Qty6 union all
    select Item7, Qty7
) c (Item, Qty);

Upvotes: 3

Related Questions