CODEWITHSUNDEEP
sql-serversql-server-2008sql-server-2012
Chintu
Chintu

Reputation: 525

Repeating table columns with single column

i have the following table

 SpareParts | JPY  | INR  | SGD  | IDR  |

   sp1         1      25     75    200
   sp2         4      33     35    400 
   sp3         5      44     77    200

i want out put like BELOW

   SpareParts  | currency  |  value

     sp1           JPY         1
     sp1           INR         25
     SP1           SGD         74
     SP1           IDR         200

     sp2           JPY         4
     sp2           INR         33
     SP2           SGD         35
     SP2           IDR         400

     sp3           JPY         5
     sp3           INR         44
     SP3           SGD         77
     SP3           IDR         200

please help me how to solve this issue.

Upvotes: 0

Views: 40

Answers (3)

Avinash Gunjuluri
Avinash Gunjuluri

Reputation: 1

you can also use a cross join to achieve the same

Select SpareParts
,CASE Value
WHEN 'JPY' THEN JPY
WHEN 'INR' THEN INR
WHEN 'SGD' THEN SGD
WHEN 'IDR' THEN IDR
END AS Currency
,Value
from Currency
Cross JOIN (VALUES ('JPY'),('INR'),('SGD'),('IDR')) AS CC(Value)

Upvotes: 0

Tanner
Tanner

Reputation: 22743

Here's an example of using UNPIVOT on your data to format it as requested:

-- create a temp table with your sample data
SELECT * INTO #temp
FROM  
    (SELECT 'sp1' as spareParts, 1 as JPY, 25 as INR, 75 as SGD, 200 as IDR
     UNION
     SELECT 'sp2' as spareParts, 4 as JPY, 33 as INR, 35 as SGD, 400 as IDR
     UNION
     SELECT 'sp3' as spareParts, 5 as JPY, 44 as INR, 75 as SGD, 200 as IDR) as t

-- output the sample data in current format
SELECT * FROM #temp

-- preform the unpivot and show reformatted data
SELECT SpareParts, Currency, Value
FROM 
   (SELECT SpareParts, JPY, INR, SGD, IDR
   FROM #temp) t
UNPIVOT
   (Value FOR Currency IN 
      (JPY, INR, SGD, IDR)
)AS unpvt

You will be able to run this as it is in a new query window in management studio, to see it working.

Upvotes: 1

MarkD
MarkD

Reputation: 5316

The operation you are attempting is generally referred to an an UNPIVOT. While SQL Server offers UNPIVOT as an operation, I've found the following pattern to be generally more efficient;

;WITH SpareParts (SpareParts, JPY, INR,SGD, IDR) AS
(
   SELECT 'sp1', 1, 25, 75, 200 UNION ALL
   SELECT 'sp2', 4, 33, 35, 400 UNION ALL
   SELECT 'sp3', 5, 44, 77, 200  
)
SELECT   SP.SpareParts
        ,CA.*
FROM SpareParts SP
CROSS 
APPLY   (
            SELECT 'JPY', JPY   UNION ALL
            SELECT 'INR', INR   UNION ALL
            SELECT 'SGD', SGD   UNION ALL
            SELECT 'IDR', IDR
        )       CA (Currency, [Value])

Upvotes: 1

Related Questions