0

I have data in a table in a 'key-value' pairing format - for each Id you can have multiple key/value pairing values with additional column specifying Table value.

DataTable:

ID    Table  Key     Value
--    --     --      --
221   Tab1   Field2  Jon
221   Tab1   Field5  Snow
221   Tab1   Field4  Male
221   Tab1   Field3  Pass
2256  Tab1   Field2  Jil
2256  Tab1   Field5  Dark

Data in Key column refers to actual column name refered to by (table)data in Table column.

Now, I want pivot this data such that each ID is only represented as a single row in relevant table and data copied to appropriate column:

Tab1:

Id   Field1 Field2  Field3  Field4  Field5  Field6
--   --     --      --      --      --      --
221         Jon     Pass    Male    Snow
2256        Jil                     Dark

How do I go about writing this?

Ilyes
  • 14,640
  • 4
  • 29
  • 55
Sandiyan
  • 1
  • 2
  • Considering that you have no row with the value `Field6` but have a column for it, this suggest you have an indeterminate number of values; correct? Have you looked up a dynamic Pivot? There are quite a few examples on Stack Overflow on how to use them. Can you show us what you've tried? – Thom A Jun 03 '19 at 21:21
  • 1
    Possible duplicate of [Convert Rows to columns using 'Pivot' in SQL Server](https://stackoverflow.com/questions/15931607/convert-rows-to-columns-using-pivot-in-sql-server) – Clockwork-Muse Jun 03 '19 at 21:57
  • 1
    This overall style is called [entity-attribute-value](https://en.wikipedia.org/wiki/Entity%E2%80%93attribute%E2%80%93value_model), and can have significant penalties, even (or perhaps especially) when implemented well. You may want to reevaluate whether you really have such a sparse dataset. – Clockwork-Muse Jun 03 '19 at 22:02

1 Answers1

2

First, sample DDL/DML statements:

CREATE TABLE t (ID INT, [Table] VARCHAR(5), [Key] varchar(6), Value varchar(5))
INSERT INTO t VALUES (221, 'Tab1', 'Field2', 'Jon'),
(221 ,'Tab1','Field5','Snow'),
(221 ,'Tab1','Field4','Male'),
(221 ,'Tab1','Field3','Pass'),
(2256,'Tab1','Field2','Jil '),
(2256,'Tab1','Field5','Dark')

A static pivot query with hard-coded columns looks like this:

SELECT *
FROM t 
PIVOT (MAX(Value) FOR [Key] IN ([Field1], [Field2], [Field3], [Field4], [Field5], [Field6]))p

Returns:

ID      Table   Field1  Field2  Field3  Field4  Field5  Field6
221     Tab1    NULL    Jon     Pass    Male    Snow    NULL
2256    Tab1    NULL    Jil     NULL    NULL    Dark    NULL

If you need to dynamically determine the columns from the data, here's an approach using sp_executesql:

DECLARE @sql NVARCHAR(MAX)
SELECT @sql = N'
SELECT p.*
FROM t
PIVOT (MAX(Value) FOR [Key] IN (' + 
    STUFF((SELECT DISTINCT ',' + QUOTENAME([Key]) FROM t FOR XML PATH('')), 1, 1, '') + '))p
'
EXEC sp_executesql @sql

Returns:

ID      Table   Field2  Field3  Field4  Field5
221     Tab1    Jon     Pass    Male    Snow
2256    Tab1    Jil     NULL    NULL    Dark

This line:

SELECT STUFF((SELECT DISTINCT ',' + QUOTENAME([Key]) FROM t FOR XML PATH('')), 1, 1, '')

just brackets and delimits the columns into this string:

[Field2],[Field3],[Field4],[Field5]
Max Szczurek
  • 4,324
  • 2
  • 20
  • 32