SQL Server get rows from table and display in columns

Question

I am a beginner with SQL and have a question. I have a table with user data like this

uid  nid val   
+---+---+---+
| 1 | x | 3 |
+---+---+---+
| 2 | x | 3 |
+---+---+---+
| 3 | x | 3 |
+---+---+---+
| 1 | y | 4 |
+---+---+---+
| 2 | y | 4 |
+---+---+---+
| 3 | y | 4 |
+---+---+---+
| 1 | z | 5 |
+---+---+---+

Where uid is a user ID, nid is a Name id which is used to identify a value and val is the actual value.

I want to write a query which gives the following result

uid   x   y   z
+---+---+---+---+
| 1 | 3 | 4 | 5 |
+---+---+---+---+
| 2 | 3 | 4 | 0 |
+---+---+---+---+
| 3 | 3 | 4 | 0 |
+---+---+---+---+

Are there functions or statements that can read data from multiple rows and put it in columns ?

Read about PIVOT or conditional aggregation

Gottfried Lesigang
– Gottfried Lesigang

2017-06-20 11:37:18 +00:00
Commented Jun 20, 2017 at 11:37 — Gottfried Lesigang
– Gottfried Lesigang, Commented Jun 20, 2017 at 11:37

Gordon Linoff · Accepted Answer · 2017-06-20 11:38:57Z

2

Your data is stored in a struct called entity-attribute value (EAV). One way of "flattening" it is to use group by or pivot:

select uid,
       max(case when nid = 'x' then val end) as x,
       max(case when nid = 'y' then val end) as y,
       max(case when nid = 'z' then val end) as z
from t
group by uid;

If you don't know the specific values you want in the result table, then you need dynamic SQL. The query is quite a bit more challenging. I would suggest that you start by Googling "SQL Server dynamic pivot".

answered Jun 20, 2017 at 11:38

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Nithin Over a year ago

Will this work if the val are different for different uid ?

Gordon Linoff Over a year ago

@Nithin . . . Yes. It will return NULL for the ids that don't have a value.

user7715598 · Accepted Answer · 2017-06-20 13:44:57Z

We Can Achieve Desired result using Dynamic Sql

IF OBJECT_ID('Tempdb..#Temp') IS NOt NUll
Drop Table #Temp
;With cte(uid,  nid, val)
AS
(
SELECT 1 , 'x', 3 UNION ALL
SELECT 2 , 'x', 3 UNION ALL
SELECT 3 , 'x', 3 UNION ALL
SELECT 1 , 'y', 4 UNION ALL
SELECT 2 , 'y', 4 UNION ALL
SELECT 3 , 'y', 4 UNION ALL
SELECT 1 , 'z', 5 
)
SELECT * INTO #Temp FRom cte

DECLARE @dynamicCol nvarchar(max),
        @Sql nvarchar(max),
        @dynamicCol2 nvarchar(max)

SELECT @dynamicCol=STUFF((SELECT DISTINCT ', ' + 'ISNULL('+nid+',''0'') AS '+QUOTENAME(nid)   FROM  #Temp
FOR XML PATH('')),1,1,'')

SELECT @dynamicCol2=STUFF((SELECT DISTINCT ', ' + QUOTENAME(nid)   FROM  #Temp
FOR XML PATH('')),1,1,'')

SET @Sql='
            SELECT [uid] , '+ @dynamicCol +' From
            (
            SELECT * From
            #temp
            )AS Src
            PIVOT 
            (
            MAX([val]) For [nid ] IN ('+@dynamicCol2+')
            )
            AS Pvt
            '

PRINT @Sql

EXEC(@Sql)

OutPut

uid x   y   z
--------------
1   3   4   5
2   3   4   0
3   3   4   0

Collectives™ on Stack Overflow

SQL Server get rows from table and display in columns

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related