single row multiple columns into one column using sql server

Question

I have table like this

OrderNo  Item_Description1  Rate1  Quantity1  Item_Description2  Rate2  Quantity2  Item_Description3  Rate3  Quantity3 
-------- ------------------ ------ ---------- ------------------ ------ ---------- ------------------ ------ ----------
1001     Laptop             50000  8          Air Conditioner    20000  10         Television         25000  12
1002     Washing Machine    35000  10         Camera             4000   20         Speaker            1500   15

From this I need to create a temp table or table like this:

OrderNo  Item_Description   Rate   Quantity 
-------- ------------------ ------ ----------
1001     Laptop             50000  8
         Air Conditioner    20000  10
         Television         25000  12
1002     Washing Machine    35000  10
         Camera             4000   20
         Speaker            1500   15

Is there a way I can do this in SQL Server?

You should remember that Tables in SQL have no implicit order of their own. If you make a temp table exactly as you show above, you will have no way to keep it in that order, so you might want to keep the Order_No column filled, or add a column for sequencing/grouping. — RBarryYoung
– RBarryYoung, Commented Aug 30, 2013 at 14:36

Taryn · Accepted Answer · 2013-08-30 14:29:31Z

7

You can also use CROSS APPLY to unpivot the data:

select t.order_no,
  c.item_description,
  c.rate,
  c.quantity
from yourtable t
cross apply
(
  select item_description1, rate1, quantity1 union all
  select item_description2, rate2, quantity2 union all
  select item_description3, rate3, quantity3
) c (item_description, rate, quantity)

answered Aug 30, 2013 at 14:29

Taryn

249k57 gold badges374 silver badges409 bronze badges

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

Comments

Sonam · Accepted Answer · 2013-08-30 14:07:33Z

2

 SELECT * FROM 
 (select ORDER_NO,ITEM_DESCRIPTION1,RATE1,QUANTITY1FROM TABLE
 UNION
 select ORDER_NO,ITEM_DESCRIPTION2,RATE2,QUANTITY2 FROM TABLE
 UNION
 select ORDER_NO,ITEM_DESCRIPTION3,RATE3,QUANTITY3 FROM TABLE)AS A ORDER BY ORDER_NO

answered Aug 30, 2013 at 14:07

Sonam

3,4861 gold badge14 silver badges25 bronze badges

1 Comment

dnoeth Over a year ago

Just one remark: use UNION ALL instead to avoid the DISTINCT processing.

sivareddy · Accepted Answer · 2013-08-31 06:56:22Z

1

Try this

SELECT t.*
FROM Table1
OUTER APPLY 
(
    VALUES 
        ([OrderNo],item_description1, rate1, quantity1),
        (NULL, item_description2, rate2, quantity2),
        (NULL, item_description3, rate3, quantity3)
) t([OrdNo],item_description, rate, quantity)

SQL FIDDLE DEMO

Or use @bluefeet answer with NULL

SELECT c.[OrderNo],
  c.item_description,
  c.rate,
  c.quantity
FROM Table1 t
CROSS APPLY
(
  SELECT [OrderNo],item_description1, rate1, quantity1   UNION ALL
  SELECT NULL, item_description2, rate2, quantity2   UNION ALL
  SELECT NULL, item_description3, rate3, quantity3
) c ([OrderNo],item_description, rate, quantity)

SQL FIDDLE DEMO

edited Aug 31, 2013 at 6:56

answered Aug 31, 2013 at 6:44

sivareddy

484 bronze badges

Comments

very9527 · Accepted Answer · 2013-08-30 14:41:04Z

0

hope to help you!

select t.* from 
 (  
   select order_No, Item_Description1 as Item_Desription, Rate1 as Rate
   from Table
   union 
   select order_No, Item_Description2 as Item_Desription, Rate2 as Rate
   from Table
   union 
   select order_No, Item_Description3 as Item_Desription, Rate3 as Rate
   from Table
) as t
Order by t.order_No asc

this is my test

the Table

select t.* from 
  (select id, apple1 as apple, orange1 as orange
  from Test
  union all 
  select id, apple2 as apple, orange2 as orange
  from Test
  union all 
  select id, apple3 as apple, orange3 as orange
  from Test) as t
order by t.id asc

the result

edited Aug 30, 2013 at 14:41

answered Aug 30, 2013 at 14:20

very9527

9597 silver badges9 bronze badges

1 Comment

very9527 Over a year ago

i could have a try by myself

Collectives™ on Stack Overflow

single row multiple columns into one column using sql server

4 Answers 4

Comments

1 Comment

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

1 Comment

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related