CODEWITHSUNDEEP
sqlsql-servert-sql
D.Alex
D.Alex

Reputation: 39

Spliting rows into columns when value occurs in SQL Server

I have a a table with this values :

ITPROD  PROD1   PROD2   Quantity 1  Quantity 2
45842   69640   63908      3               2
70690   91387   90734      1               2

The result table should be :

ITPROD   PROD       Quantity    
45842    69640         3           
45842    63908         2           
70690    91387         1           
70690    90734         2

Upvotes: 0

Views: 40

Answers (3)

SqlZim
SqlZim

Reputation: 38063

Using cross apply() with values():

select 
    t.itprod
  , v.Prod
  , v.Quantity
from t
  cross apply (values 
      (Prod1,Quantity1)
    , (Prod2,Quantity2)
    ) v(Prod,Quantity)

rextester demo: http://rextester.com/MFDCA68129

returns:

+--------+-------+----------+
| itprod | Prod  | Quantity |
+--------+-------+----------+
|  45842 | 69640 |        3 |
|  45842 | 63908 |        2 |
|  70690 | 91387 |        1 |
|  70690 | 90734 |        2 |
+--------+-------+----------+

Upvotes: 1

SHD
SHD

Reputation: 409

Try This,

SELECT ITPROD   AS ITPROD,PROD1  AS PROD,Quantity1  AS Quantity1 FROM SampleTable
UNION ALL
SELECT ITPROD   AS ITPROD,PROD2  AS PROD,Quantity2  AS Quantity1 FROM SampleTable

Upvotes: 0

juergen d
juergen d

Reputation: 204904

select itprod, prof1 as prod, quantity1 as quantity from your_table
union all
select itprod, prof2, quantity2 from your_table

Upvotes: 1

Related Questions