Pivoting row values in one column based on values in two other columns

Question

To expand upon my last question regarding a SQL query or C# code that can pivot or transpose values in one column into distinct columns based on data from another column:

Upon further inspection of the data I need a script or query that can convert row values in one column into distinct columns based on values in two other columns.

So for example, here is my current data table:

ID      USERID     ATTRIBUTE       ATTRIBUTE VALUE 
00001   15         Entitlement       80
00001   15         Entitlement       81
00001   15         Permission        90
00001   15         Permission        91
00001   15         Permission        92
00001   16         Entitlement       82
00001   16         Permission        93

I would need the output table to look like:

ID      USERID        ENTITLEMENT     PERMISSION 
00001   15               80           90
00001   15               81           91
00001   15                            92
00001   16               82           93

Where the values in the ENTITLEMENT and PERMISSION column are grouped by USERID.

I understand there will be Null values in either new columns when the row count between them doesn't match, and that's ok, as long as each new column contains all the row values and correspond with the correct USERID.

Pivoting the data using T-SQL, i.e.

select ID, USERID, ENTITLEMENT, PERMISSION from dbo.Test_Table
pivot (Max(ATTRIBUTE VALUE) for Attribute IN ([ENTITLEMENT], [PERMISSION])) as PivotTable
order by ID asc

...doesn't return all values due to the MAX aggregate.

The C# code in the previous answer works fine, but, again, only considers ATTRIBUTE VALUE column.

Once again, any help with this task is appreciated. C# is not my forte.

score 2 · Accepted Answer · answered Jun 20 '19 at 13:14

You could use the following with use of DENSE_RANK

SELECT 
      CASE WHEN A.ID IS NULL THEN B.ID ELSE A.ID END AS ID,
      CASE WHEN A.USERID IS NULL THEN B.USERID ELSE A.USERID END AS USERID,
      A.[ATTRIBUTEVALUE] ENTITLEMENT,
      B.[ATTRIBUTEVALUE] PERMISSION 
FROM
    (
      SELECT *,
      DENSE_RANK() over (partition by  ATTRIBUTE order by  ATTRIBUTEVALUE)  RN1 
      FROM  <<yourtable>>
      WHERE ATTRIBUTE = 'Entitlement'
     )A
FULL OUTER JOIN
     (
      SELECT *,
      DENSE_RANK() over (partition by  ATTRIBUTE order by  ATTRIBUTEVALUE)  RN2 
      FROM <<yourtable>>
      WHERE ATTRIBUTE = 'Permission'
      )B  ON A.RN1 = B.RN2
ORDER BY 2,3

output look like as follows

 ID         USERID      ENTITLEMENT PERMISSION
 ---------- ----------- ----------- -----------
 00001      15          80          90
 00001      15          81          91
 00001      16          NULL        93
 00001      16          82          92

unfortunately the how you want the data to look would not be supported by PIVOT. because you want to show multiple rows for a same id and user.

score 0 · Answer 2 · answered Jun 19 '19 at 17:28

You can try this following script to get your desired output-

SELECT 
CASE WHEN A.ID IS NULL THEN B.ID ELSE A.ID END AS ID,
CASE WHEN A.USERID IS NULL THEN B.USERID ELSE A.USERID END AS USERID,
A.[ATTRIBUTE VALUE] ENTITLEMENT,
B.[ATTRIBUTE VALUE] PERMISSION 
FROM
(
    SELECT *,ROW_NUMBER() OVER(PARTITION BY USERID ORDER BY USERID) RN1 FROM your_table
    WHERE ATTRIBUTE = 'Entitlement'
)A
FULL OUTER JOIN
(
    SELECT *,ROW_NUMBER() OVER(PARTITION BY USERID ORDER BY USERID) RN2 FROM your_table
    WHERE ATTRIBUTE = 'Permission'
)B
ON A.RN1 = B.RN2 AND A.USERID = B.USERID
ORDER BY 2

Output is-

ID      USERID  ENTITLEMENT PERMISSION 
00001   15      80          90
00001   15      81          91
00001   15      NULL            92
00001   16      82          93

Pivoting row values in one column based on values in two other columns

2 Answers2