CODEWITHSUNDEEP
sqlsql-server
cup
cup

Reputation: 8237

Reformatting SQL output

I have data that looks like this

Name  XX YY
alpha 10 77
beta  10 90
alpha 20 72
beta  20 91
alpha 30 75
beta  30 94
alpha 40 76
beta  40 95

If I use

select * from scores order by Name, XX

I will get

Name  XX YY
alpha 10 77
alpha 20 72
alpha 30 75
alpha 40 76
beta  10 90
beta  20 91
beta  30 94
beta  40 95

At the moment, I'm retrieving the data in this form and using some C coding to format it like

Name   xx=10  xx=20  xx=30  xx=40
alpha  77     72     75     76
beta   90     91     94     95

Assuming that there will always be entries for 10, 20, 30, 40 for every name, is something like this possible in SQL without creating a new table like in SQL Reformatting table columns

Upvotes: 0

Views: 86

Answers (3)

Faraz Ahmed
Faraz Ahmed

Reputation: 1607

Another solution with case

SELECT Name
  ,SUM(CASE when XX = '10' then YY else 0 END) AS 'xx=10'
  ,SUM( CASE when XX = '20' then YY else 0 END) AS 'xx=20'
  ,SUM( CASE when XX = '30' then YY else 0 END) AS 'xx=30'
  ,SUM( CASE when XX = '40' then YY else 0 END) AS 'xx=40'
  FROM temp_0
  group by Name

Upvotes: 0

MJoy
MJoy

Reputation: 1369

You could use dynamic PIVOT to achieve the same result which would be more robust,

CREATE TABLE #table1 (Name varchar(100), XX INT, YY INT)

INSERT INTO #table1 VALUES
('alpha',10,77),
('beta',10,90),
('alpha',20,72),
('beta',20,91),
('alpha',30,75),
('beta',30,94),
('alpha',40,76),
('beta',40,95)

DECLARE @pvt NVARCHAR(MAX) = '';
DECLARE @dynamicCol NVARCHAR(MAX) = '';

SELECT @pvt +=  ', ' +QUOTENAME([XX]) FROM #table1 GROUP BY [XX];
SELECT @dynamicCol +=  ', ' +QUOTENAME([XX]) + ' AS ' + QUOTENAME('XX=' + CAST([XX] AS VARCHAR(25))) FROM #table1 GROUP BY [XX];
SET @pvt = STUFF(@pvt,1,1,'')
SET @dynamicCol = STUFF(@dynamicCol,1,1,'')

EXEC ('
SELECT [Name],' + @dynamicCol+'
FROM #table1 a
PIVOT 
(
  SUM([YY])
  FOR [XX] IN ('+ @pvt+')
) PIV');

The result is as below,

Name    XX=10   XX=20   XX=30   XX=40
alpha   77      72      75      76
beta    90      91      94      95

Upvotes: 1

mkRabbani
mkRabbani

Reputation: 16908

You need to use PIVOT to get your desired results. Before using PIVOT, some customization required in your value in column XX so that the final column output can meet your expectation.

SELECT * FROM
(
SELECT Name,'XX='+CAST(XX AS VARCHAR) XX,YY 
FROM your_table
)AS P
PIVOT(
    SUM(YY)
    FOR XX IN ([XX=10],[XX=20],[XX=30],[XX=40])
) PP

Output- 

Name    XX=10   XX=20   XX=30   XX=40
alpha   77      72      75      76
beta    90      91      94      95

The same output can be also achieved with this following query-

SELECT Name,
[10] AS [XX=10],
[20] AS [XX=20],
[30] AS [XX=30],
[40] AS [XX=40]
FROM
(
    SELECT Name, XX,YY 
    FROM your_table
)AS P
PIVOT(
    SUM(YY)
    FOR XX IN ([10],[20],[30],[40])
) PP

Upvotes: 3

Related Questions