Dynamic select statement to convert recordset into a readable format

Question

I am stumped on an output query. I feel unpivot will get me there but I can't seem to get the syntax correct and could use some help. Basically, a single record set contains two columns and one column is actually the column name while the other column is the column value. Ideally I would like the output to label column 2 as column 1's result for each possible unique value. Below is a simplified sample but the final solution could contain an infinite number of attributes per pc.

create table #t (pc int, attribute varchar(100), result varchar(100) )
go
INSERT INTO #t VALUES (1,'OS','LINUX'),(1,'VERSION','10.4'),(2,'OS','WINDOWS'),(2,'VERSION','10.1903'),(3,'OS','LINUX'),(3,'VERSION','11.0'),(4,'OS','WINDOWS'),(4,'VERSION','10.1909'),(5,'OS','WINDOWS'),(5,'VERSION','10.1909'),(6,'OS','LINUX'),(6,'VERSION','10.4')
go
select * from #t

Result Set:

id pc  attribute   result
1   OS          LINUX
1   VERSION     10.4
2   OS          WINDOWS>
2   VERSION     10.1903
3   OS          LINUX
3   VERSION     11.0
4   OS          WINDOWS
4   VERSION     10.1909
5   OS          WINDOWS
5   VERSION     10.1909
6   OS          LINUX
6   VERSION     10.4

Desired Output:

pc OS          VERSION
1   LINUX       10.4
2   WINDOWS     10.1903
3   LINUX       11.0
4   WINDOWS     10.1909
5   WINDOWS     10.1909
6   LINUX       10.4

Any tips/advice you can give would be greatly appreciated. Cheers mate.

Does this answer your question? [Understanding PIVOT function in T-SQL](https://stackoverflow.com/questions/10428993/understanding-pivot-function-in-t-sql) — Charlieface, Jan 25 '21 at 14:57
That did the trick. It was the @cols string that I was using incorrectly. Thanks mate. — TStewartFan, Jan 25 '21 at 15:26

score 0 · Answer 1 · answered Mar 03 '21 at 06:17

0

The desired output can be obtained from a simple PIVOT. Refer to the below added code sample. I hope you find the answer here.

SELECT 
   PIVOTED.* 
FROM 
 (

    select * 
    from  #t

   ) AS P
PIVOT
(

   MIN(P.result)

   FOR P.attribute in ([OS],[Version])

 ) AS PIVOTED

Output from the PIVOT Query: Result:

answered Mar 03 '21 at 06:17

Gudwlk

1,177
11
11

I will add the dynamic PIVOT for this to obtained infinite number of attributes per pc. So, that you will be able to use it for n number of attributes. – Gudwlk Mar 03 '21 at 06:19

score 0 · Answer 2 · answered Mar 03 '21 at 06:51

Dynamic PIVOT Solution for the answer as you can add any number of attributes and obtained the result you need.

DECLARE @SQL  AS VARCHAR(MAX)
, @cols_ AS  VARCHAR(MAX) 

--Making the column list dynamically 
SELECT @cols_   = STUFF((SELECT DISTINCT ', '+QUOTENAME(  C.attribute) 
               FROM  #t  C                 
               FOR XML PATH('')), 1, 1, '')              

--preparing PIVOT query dynamically. 

SET @SQL = ' SELECT
             pivoted.* 
      
            FROM 
              (
               SELECT * 
               FROM   #t 
              ) AS cp
              PIVOT
               (
                  MIN([CP].[result]) 
                  FOR  [CP].[attribute] IN (' + @cols_ + ')
              ) AS pivoted;  ' ;

PRINT( @SQL) EXEC (@SQL)

I added below lines to the table #t to test the result. INSERT INTO #t VALUES (1,'SNO','0012') INSERT INTO #t VALUES (2,'SNO','0013') INSERT INTO #t VALUES (3,'SNO','0014') INSERT INTO #t VALUES (4,'SNO','0015') INSERT INTO #t VALUES (5,'SNO','0016') INSERT INTO #t VALUES (6,'SNO','0017')

Below find the result for the dynamic pivot. Dynamic PIVOT result

Dynamic select statement to convert recordset into a readable format

2 Answers2