CODEWITHSUNDEEP
sqlsql-serversql-server-2008t-sql
Mathi
Mathi

Reputation: 173

single row multiple columns into one column using sql server

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?

Upvotes: 2

Views: 5040

Answers (4)

sivareddy
sivareddy

Reputation: 48

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

Upvotes: 1

very9527
very9527

Reputation: 959

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

Upvotes: 0

Taryn
Taryn

Reputation: 247690

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)

Upvotes: 7

Sonam
Sonam

Reputation: 3466

 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

Upvotes: 2

Related Questions