Tags

, , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , ,


SQL Puzzle | Simple Pivot – Facebook Puzzle

Puzzle Statement

This puzzle was asked in social media ( Facebook ), In this puzzle you have to convert a single column data into multiple columns. We can say its a simple pivot puzzle. The Puzzle says-

Pawan Khowal | SQL Puzzle - Facebook

Pawan Khowal | SQL Puzzle – Facebook

Check out the input and output required for this puzzle is-

Sample Input

Country_cost
12.09
23.09
34.76
23.65
456.098

Expected output

A_Cost B_Cost C_Cost D_Cost E_Cost
12.09 23.09 34.76 23.65 456.098

 

Script

Use the below script to generate the source table and fill them up with the sample data.

--

CREATE TABLE CountryCost
(
	Country_cost FLOAT
)
GO

INSERT INTO CountryCost(Country_cost) 
VALUES (12.09),(23.09),(34.76),(23.65),(456.098)
GO

--

Rules/Restrictions

The solution should use “SELECT” statement or “CTE”.
Add your solution(s) in the comments section or send you solution(s) to pawankkmr@gmail.com

Solution #

--

;WITH CTE AS
(
	SELECT Country_cost , ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) rnk FROM CountryCost
)
SELECT [1] A_Cost , [2] B_Cost , [3] C_Cost , [4] D_Cost, [5] E_Cost FROM 
CTE 
PIVOT (MAX(Country_cost) FOR rnk IN ([1],[2],[3],[4],[5])) p


--

Add a comment if you have any other solution in mind. We all need to learn. Enjoy !!!

Keep Learning

Pawan Khowal 

Http://MSBISkills.com

Advertisements