CODEWITHSUNDEEP
sqlsql-server
user9934918
user9934918

Reputation: 49

How do I change repeated values in a column to rows and then populate the respective row values with the respective values

I have a table this way:

Table image

ID  ATTR_NAME   SUB_ATTR_NAME   VALUE
1   ATTR-1        SUB-ATTR-1    23
2   ATTR-1        SUB-ATTR-2    32
3   ATTR-1        SUB-ATTR-3    25
4   ATTR-1        SUB-ATTR-4    28
5   ATTR-2        SUB-ATTR-1    78
6   ATTR-2        SUB-ATTR-2    45
7   ATTR-2        SUB-ATTR-3    48
8   ATTR-2        SUB-ATTR-4    41
9   ATTR-3        SUB-ATTR-1    47
10  ATTR-3        SUB-ATTR-2    12
11  ATTR-3        SUB-ATTR-3    16
12  ATTR-3        SUB-ATTR-4    18

But using SQL, I want a table this way:

Output Image

            SUB-ATTR-1  SUB-ATTR-2  SUB-ATTR-3  SUB-ATTR-4
ATTR-1      23          32          25          28
ATTR-2      78          45          48          41
ATTR-3      47          12          16          18

Please help I am a newbie to SQL

Upvotes: 0

Views: 97

Answers (2)

Sreenu131
Sreenu131

Reputation: 2516

Sample Data

IF OBJECT_ID('tempdb..#Temp') IS NOT NULL
DROP TABLE #Temp
CREATE TABLE #Temp(ID INT, ATTR_NAME VARCHAR(20),  SUB_ATTR_NAME VARCHAR(20),VALUE INT)
INSERT INTO #Temp

SELECT 1 ,'ATTR-1','SUB-ATTR-1', 23 UNION ALL
SELECT 2 ,'ATTR-1','SUB-ATTR-2', 32 UNION ALL
SELECT 3 ,'ATTR-1','SUB-ATTR-3', 25 UNION ALL
SELECT 4 ,'ATTR-1','SUB-ATTR-4', 28 UNION ALL
SELECT 5 ,'ATTR-2','SUB-ATTR-1', 78 UNION ALL
SELECT 6 ,'ATTR-2','SUB-ATTR-2', 45 UNION ALL
SELECT 7 ,'ATTR-2','SUB-ATTR-3', 48 UNION ALL
SELECT 8 ,'ATTR-2','SUB-ATTR-4', 41 UNION ALL
SELECT 9 ,'ATTR-3','SUB-ATTR-1', 47 UNION ALL
SELECT 10,'ATTR-3','SUB-ATTR-2', 12 UNION ALL
SELECT 11,'ATTR-3','SUB-ATTR-3', 16 UNION ALL
SELECT 12,'ATTR-3','SUB-ATTR-4', 18
SELECT * FROM #Temp

Using Dynamic Sql

DECLARE @Sql nvarchar(max),
        @Col nvarchar(max),
        @Col2 nvarchar(max)

SELECT @Col=STUFF((SELECT DISTINCT ', '+QUOTENAME(SUB_ATTR_NAME)  FROM #Temp
FOR XML PATH ('')),1,1,'')
SELECT @Col2=STUFF((SELECT DISTINCT ', '+'MAX( '+QUOTENAME(SUB_ATTR_NAME)+' ) AS'+QUOTENAME(SUB_ATTR_NAME)   FROM #Temp
FOR XML PATH ('')),1,1,'')

SET @Sql='
SELECT ATTR_NAME,'+@Col2+' FROM
(
SELECT * FROM #Temp 
)AS SRC
PIVOT
(
SUM(VALUE) FOR SUB_ATTR_NAME IN ('+@Col+')
)AS PVT
GROUP BY ATTR_NAME'
PRINT @Sql
EXEC (@Sql)

Result

ATTR_NAME   SUB-ATTR-1  SUB-ATTR-2  SUB-ATTR-3  SUB-ATTR-4
----------------------------------------------------------
ATTR-1          23          32          25          28
ATTR-2          78          45          48          41
ATTR-3          47          12          16          18

Demo :http://rextester.com/SURJ9296

Upvotes: 2

Pelin
Pelin

Reputation: 966

You need to group your table by ATTR_NAME, then you can get sum of the SUB_ATTR 

select attr_name ,
sum( case CASE  
     WHEN  SUB_ATTR_NAME = 'SUB-ATTR-1' THEN VALUE
     else 0 end) SUB-ATTR-1,
sum( case CASE  
     WHEN  SUB_ATTR_NAME = 'SUB-ATTR-2' THEN VALUE
     else 0 end) SUB-ATTR-2,
sum( case CASE  
     WHEN  SUB_ATTR_NAME = 'SUB-ATTR-3' THEN VALUE
     else 0 end) SUB-ATTR-3,
sum( case CASE  
     WHEN  SUB_ATTR_NAME = 'SUB-ATTR-4' THEN VALUE
     else 0 end) SUB-ATTR-4
END   sum(value)
from TABLE
group by attr_name

Upvotes: 1

Related Questions