I have a requirement where I have nvarchar column in the database, which I need to pull in the form of row and Column from the database.
我有一个要求，我在数据库中有nvarchar列，我需要从数据库中以行和列的形式提取。
My nvarchar is like
我的nvarchar就像
{
"notifications": [
{
"apMacAddress": "20:50:7a:09:5t:30",
"associated": false,
"band": "IEEE_802_11_A",
"confidenceFactor": 192,
"deviceId": "34:f4:23:23:f4:wr",
"entity": "Wired",
"eventId": 2343434,
"floorId": 3244234234234,
"floorRefId": 234235465234,
"geoCoordinate": {
"latitude": -999.0,
"longitude": -999.0,
"unit": "DEGREES"
},
"ipAddress": [
"23.423.23.234"
],
"lastSeen": "2018-02-09T12:24:53.512+0000",
"locationCoordinate": {
"x": 423.2342,
"y": 23.423,
"z": 0.0,
"unit": "FEET"
},
"locationMapHierarchy": "North Campus>North SIde>F4>F4 Academies ",
"manufacturer": "Sony Corporate",
"maxDetectedRssi": {
"antennaIndex": 0,
"apMacAddress": "234:23:56d:45d:5f:20",
"band": "IEEE_802_11_B",
"lastHeardInSeconds": 23,
"rssi": -54,
"slot": 2
},
"notificationType": "locationupdate",
"rawLocation": {
"RawX": 0.0,
"RawY": 0.0,
"Unit": null
},
"ssid": "aide",
"subscriptionName": " Lab Location",
"tagVendorData": null,
"timestamp": 2343657436,
"username": "abc.gmail.com"
}
]
}
And I want data to be pulled up like separate columns
我希望数据像单独的列一样被拉出来
Here are the list of column names
以下是列名列表
- apMacAddress
- associated
- band
- confidenceFactor
- deviceId
- entity
- eventId
- floorId
- floorRefId
- latitude
- longitude
- unit
- ipAddress
- lastSeen
- X
- Y
- Z
- Unit
- locationMapHierarchy
- manufacturer
- antennaIndex
- apMacAddress
- band
- lastHeardInSeconds
- rssi
- slot
- notificationType
- RawX
- RawY
- Unit
- ssid
- subscriptionName
- tagVendorData
- timestamp
- username
is there any way to do this in SQL?
有没有办法在SQL中这样做？
2 个解决方案
#1
3
That's not unstructured data, that's JSON data, a structured format. You can't parse JSON by string splitting. SQL Server 2016 added JSON support, as shown in Work with JSON data in SQL Server
这不是非结构化数据，即JSON数据，结构化格式。您无法通过字符串拆分来解析JSON。 SQL Server 2016添加了JSON支持，如在SQL Server中使用JSON数据中所示
You can use the OPENJSON function to parse the data and extract properties by specifying their path, eg:
您可以使用OPENJSON函数来解析数据并通过指定其路径来提取属性，例如：
DECLARE @json NVARCHAR(MAX)
SET @json =
N'{"notifications":[{"apMacAddress":"20:50:7a:09:5t:30","associated":false,"band":"IEEE_802_11_A","confidenceFactor":192,"deviceId":"34:f4:23:23:f4:wr","entity":"Wired","eventId":2343434,"floorId":3244234234234,"floorRefId":234235465234,"geoCoordinate":{"latitude":-999.0,"longitude":-999.0,"unit":"DEGREES"},"ipAddress":["23.423.23.234"],"lastSeen":"2018-02-09T12:24:53.512+0000","locationCoordinate":{"x":423.2342,"y":23.423,"z":0.0,"unit":"FEET"},"locationMapHierarchy":"North Campus>North SIde>F4>F4 Academies ","manufacturer":"Sony Corporate","maxDetectedRssi":{"antennaIndex":0,"apMacAddress":"234:23:56d:45d:5f:20","band":"IEEE_802_11_B","lastHeardInSeconds":23,"rssi":-54,"slot":2},"notificationType":"locationupdate","rawLocation":{"RawX":0.0,"RawY":0.0,"Unit":null},"ssid":"aide","subscriptionName":" Lab Location","tagVendorData":null,"timestamp":2343657436,"username":"abc.gmail.com"}]}'
SELECT *
FROM OPENJSON(@json, '$.notifications')
with (
apMacAddress nvarchar(500) '$.apMacAddress',
associated bit '$.associated',
band nvarchar(50) '$.band'
)
This will return :
这将返回：
apMacAddress associated band
20:50:7a:09:5t:30 0 IEEE_802_11_A
To parse nested structures, you need to extract the structure with the AS JSON clause and apply OPENJSON to it again with CROSS APPLY, eg:
要解析嵌套结构，需要使用AS JSON子句提取结构，并使用CROSS APPLY再次将OPENJSON应用于它，例如：
SELECT apMacAddress,associated,band,latitude,longitude
FROM OPENJSON(@json, '$.notifications')
with (
apMacAddress nvarchar(500) '$.apMacAddress',
associated bit '$.associated',
band nvarchar(50) '$.band',
geoCoordinate nvarchar(max) AS JSON
)
CROSS APPLY OPENJSON(geoCoordinate) with (
latitude decimal(5,2) '$.latitude',
longitude decimal(5,2) '$.longitude'
)
Will return :
将返回 ：
apMacAddress associated band latitude longitude
20:50:7a:09:5t:30 0 IEEE_802_11_A -999.00 -999.00
In earlier versions you have to parse the JSON strings before inserting them into the database, eg using JSON.NET, the most popular JSON parser for .NET.
在早期版本中，您必须在将JSON字符串插入数据库之前对其进行解析，例如使用JSON.NET，这是最流行的.NET JSON解析器。
While there are some JSON parser implementations for earlier versions, like this one they are complex, slow and hard to use
虽然早期版本有一些JSON解析器实现，但就像这一样，它们很复杂，速度慢且难以使用
UPDATE
UPDATE
To be clear, OPENJSON is a function like any other table-valued function eg STRING_SPLIT. This means it can be applied to a field using CROSS APPLY:
要清楚，OPENJSON是一个函数，就像任何其他表值函数，例如STRING_SPLIT。这意味着它可以使用CROSS APPLY应用于字段：
SELECT *
from SomeTable
CROSS APPLY OPENJSON(SomeTable.MyJsonField, '$.notifications')
with (
apMacAddress nvarchar(500) '$.apMacAddress',
associated bit '$.associated',
band nvarchar(50) '$.band',
geoCoordinate nvarchar(max) AS JSON
)
CROSS APPLY OPENJSON(geoCoordinate)
with (
latitude decimal(5,2) '$.latitude',
longitude decimal(5,2) '$.longitude'
)
To extract more nested structures, they have to be extracted at the top, then parsed with OPENJSON just like geoCoordinate:
要提取更多嵌套结构，必须在顶部提取它们，然后使用OPENJSON解析，就像geoCoordinate一样：
SELECT apMacAddress,associated,band,latitude,longitude ,x,y
FROM OPENJSON(@json, '$.notifications')
with (
apMacAddress nvarchar(500) '$.apMacAddress',
associated bit '$.associated',
band nvarchar(50) '$.band',
geoCoordinate nvarchar(max) AS JSON,
locationCoordinate nvarchar(max) AS JSON
)
CROSS APPLY OPENJSON(geoCoordinate)
with (
latitude decimal(5,2) '$.latitude',
longitude decimal(5,2) '$.longitude'
)
CROSS APPLY OPENJSON(locationCoordinate) with (
x decimal(5,2) '$.x',
y decimal(5,2) '$.y'
)
Will return the locationCoordinate.x and locationCoordinate.y values :
将返回locationCoordinate.x和locationCoordinate.y值：
apMacAddress associated band latitude longitude x y
20:50:7a:09:5t:30 0 IEEE_802_11_A -999.00 -999.00 423.23 23.42
#2
1
In addition to Panagiotis Kanavos reply, suppose you are using SQL Server 2016 and onward (with Compatibility level 130) you can use the OPENJSON function.
除了Panagiotis Kanavos的回复之外，假设您使用的是SQL Server 2016及更高版本（兼容级别130），您可以使用OPENJSON功能。
Another assumption is that you Json structure will not change you can use the following to parse the elements into columns
另一个假设是你的Json结构不会改变你可以使用以下内容将元素解析成列
UPDATE(!!)
UPDATE（!!）
SET NOCOUNT ON
IF OBJECT_ID ('tempdb..##DataSet') IS NOT NULL DROP TABLE ##DataSet
DECLARE @Table TABLE (Id int identity (1,1) , JsonCol NVARCHAR(MAX))
INSERT INTO @Table (JsonCol)
VALUES ('{
"notifications": [
{
"apMacAddress": "20:50:7a:09:5t:30",
"associated": false,
"band": "IEEE_802_11_A",
"confidenceFactor": 192,
"deviceId": "34:f4:23:23:f4:wr",
"entity": "Wired",
"eventId": 2343434,
"floorId": 3244234234234,
"floorRefId": 234235465234,
"geoCoordinate": {
"latitude": -888.0,
"longitude": -888.0,
"unit": "DEGREES"
},
"ipAddress": [
"23.423.23.234"
],
"lastSeen": "2018-02-09T12:24:53.512+0000",
"locationCoordinate": {
"x": 423.2342,
"y": 23.423,
"z": 0.0,
"unit": "FEET"
},
"locationMapHierarchy": "North Campus>North SIde>F4>F4 Academies ",
"manufacturer": "Sony Corporate",
"maxDetectedRssi": {
"antennaIndex": 0,
"apMacAddress": "234:23:56d:45d:5f:20",
"band": "IEEE_802_11_B",
"lastHeardInSeconds": 23,
"rssi": -54,
"slot": 2
},
"notificationType": "locationupdate",
"rawLocation": {
"RawX": 0.0,
"RawY": 0.0,
"Unit": null
},
"ssid": "aide",
"subscriptionName": " Lab Location",
"tagVendorData": null,
"timestamp": 2343657436,
"username": "abc.gmail.com"
}
]
}'),
('{
"notifications": [
{
"apMacAddress": "20:50:7a:09:5t:30",
"associated": false,
"band": "IEEE_802_11_A",
"confidenceFactor": 192,
"deviceId": "34:f4:23:23:f4:wr",
"entity": "Wired",
"eventId": 2343434,
"floorId": 3244234234234,
"floorRefId": 234235465234,
"geoCoordinate": {
"latitude": -999.0,
"longitude": -999.0,
"unit": "DEGREES"
},
"ipAddress": [
"23.423.23.234"
],
"lastSeen": "2018-02-09T12:24:53.512+0000",
"locationCoordinate": {
"x": 423.2342,
"y": 23.423,
"z": 0.0,
"unit": "FEET"
},
"locationMapHierarchy": "North Campus>North SIde>F4>F4 Academies ",
"manufacturer": "Sony Corporate",
"maxDetectedRssi": {
"antennaIndex": 0,
"apMacAddress": "234:23:56d:45d:5f:20",
"band": "IEEE_802_11_B",
"lastHeardInSeconds": 23,
"rssi": -54,
"slot": 2
},
"notificationType": "locationupdate",
"rawLocation": {
"RawX": 0.0,
"RawY": 0.0,
"Unit": null
},
"ssid": "aide",
"subscriptionName": " Lab Location",
"tagVendorData": null,
"timestamp": 2343657436,
"username": "abc.gmail.com"
}
]
}')
,(
'{
"notifications": [
{
"apMacAddress": "20:50:7a:09:5t:30",
"associated": false,
"band": "IEEE_802_11_A",
"confidenceFactor": 192,
"deviceId": "34:f4:23:23:f4:wr",
"entity": "Wired",
"eventId": 2343434,
"floorId": 3244234234234,
"floorRefId": 234235465234,
"geoCoordinate": {
"latitude": -777.0,
"longitude": -777.0,
"unit": "DEGREES"
},
"ipAddress": [
"23.423.23.234"
],
"lastSeen": "2018-02-09T12:24:53.512+0000",
"locationCoordinate": {
"x": 423.2342,
"y": 23.423,
"z": 0.0,
"unit": "FEET"
},
"locationMapHierarchy": "North Campus>North SIde>F4>F4 Academies ",
"manufacturer": "Sony Corporate",
"maxDetectedRssi": {
"antennaIndex": 0,
"apMacAddress": "234:23:56d:45d:5f:20",
"band": "IEEE_802_11_B",
"lastHeardInSeconds": 23,
"rssi": -54,
"slot": 2
},
"notificationType": "locationupdate",
"rawLocation": {
"RawX": 0.0,
"RawY": 0.0,
"Unit": null
},
"ssid": "aide",
"subscriptionName": " Lab Location",
"tagVendorData": null,
"timestamp": 2343657436,
"username": "abc.gmail.com"
}
]
}'
)
;WITH BrkJson as
(
SELECT *
FROM @Table
CROSS APPLY OPENJSON (JsonCol, '$.notifications[0]') R
)
,Dataset as
(
SELECT Id, [key] , [value]
FROM BrkJson
WHERE [type] != 5 /*Array*/
UNION ALL
SELECT b.Id ,b.[Key] +'_'+ t.[key] [key] , t.[value]
FROM BrkJson b
CROSS APPLY OPENJSON (value) t
WHERE b.[type] = 5
)
SELECT *
INTO ##DataSet
FROM Dataset
DECLARE @cols NVARCHAR(MAX)=''
DECLARE @pvt NVARCHAR(MAX)
SELECT @cols +=','+ QUOTENAME([key])
FROM ##DataSet
GROUP BY [key]
SET @cols = STUFF(@cols,1,1,'')
--PRINT @cols
SET @pvt =
'SELECT *
FROM ##DataSet
PIVOT
(
MAX([value]) FOR [key] in ('+@cols+')
) e
'
EXEC sp_executesql @Pvt
DROP TABLE ##DataSet
#1
3
That's not unstructured data, that's JSON data, a structured format. You can't parse JSON by string splitting. SQL Server 2016 added JSON support, as shown in Work with JSON data in SQL Server
这不是非结构化数据，即JSON数据，结构化格式。您无法通过字符串拆分来解析JSON。 SQL Server 2016添加了JSON支持，如在SQL Server中使用JSON数据中所示
You can use the OPENJSON function to parse the data and extract properties by specifying their path, eg:
您可以使用OPENJSON函数来解析数据并通过指定其路径来提取属性，例如：
DECLARE @json NVARCHAR(MAX)
SET @json =
N'{"notifications":[{"apMacAddress":"20:50:7a:09:5t:30","associated":false,"band":"IEEE_802_11_A","confidenceFactor":192,"deviceId":"34:f4:23:23:f4:wr","entity":"Wired","eventId":2343434,"floorId":3244234234234,"floorRefId":234235465234,"geoCoordinate":{"latitude":-999.0,"longitude":-999.0,"unit":"DEGREES"},"ipAddress":["23.423.23.234"],"lastSeen":"2018-02-09T12:24:53.512+0000","locationCoordinate":{"x":423.2342,"y":23.423,"z":0.0,"unit":"FEET"},"locationMapHierarchy":"North Campus>North SIde>F4>F4 Academies ","manufacturer":"Sony Corporate","maxDetectedRssi":{"antennaIndex":0,"apMacAddress":"234:23:56d:45d:5f:20","band":"IEEE_802_11_B","lastHeardInSeconds":23,"rssi":-54,"slot":2},"notificationType":"locationupdate","rawLocation":{"RawX":0.0,"RawY":0.0,"Unit":null},"ssid":"aide","subscriptionName":" Lab Location","tagVendorData":null,"timestamp":2343657436,"username":"abc.gmail.com"}]}'
SELECT *
FROM OPENJSON(@json, '$.notifications')
with (
apMacAddress nvarchar(500) '$.apMacAddress',
associated bit '$.associated',
band nvarchar(50) '$.band'
)
This will return :
这将返回：
apMacAddress associated band
20:50:7a:09:5t:30 0 IEEE_802_11_A
To parse nested structures, you need to extract the structure with the AS JSON clause and apply OPENJSON to it again with CROSS APPLY, eg:
要解析嵌套结构，需要使用AS JSON子句提取结构，并使用CROSS APPLY再次将OPENJSON应用于它，例如：
SELECT apMacAddress,associated,band,latitude,longitude
FROM OPENJSON(@json, '$.notifications')
with (
apMacAddress nvarchar(500) '$.apMacAddress',
associated bit '$.associated',
band nvarchar(50) '$.band',
geoCoordinate nvarchar(max) AS JSON
)
CROSS APPLY OPENJSON(geoCoordinate) with (
latitude decimal(5,2) '$.latitude',
longitude decimal(5,2) '$.longitude'
)
Will return :
将返回 ：
apMacAddress associated band latitude longitude
20:50:7a:09:5t:30 0 IEEE_802_11_A -999.00 -999.00
In earlier versions you have to parse the JSON strings before inserting them into the database, eg using JSON.NET, the most popular JSON parser for .NET.
在早期版本中，您必须在将JSON字符串插入数据库之前对其进行解析，例如使用JSON.NET，这是最流行的.NET JSON解析器。
While there are some JSON parser implementations for earlier versions, like this one they are complex, slow and hard to use
虽然早期版本有一些JSON解析器实现，但就像这一样，它们很复杂，速度慢且难以使用
UPDATE
UPDATE
To be clear, OPENJSON is a function like any other table-valued function eg STRING_SPLIT. This means it can be applied to a field using CROSS APPLY:
要清楚，OPENJSON是一个函数，就像任何其他表值函数，例如STRING_SPLIT。这意味着它可以使用CROSS APPLY应用于字段：
SELECT *
from SomeTable
CROSS APPLY OPENJSON(SomeTable.MyJsonField, '$.notifications')
with (
apMacAddress nvarchar(500) '$.apMacAddress',
associated bit '$.associated',
band nvarchar(50) '$.band',
geoCoordinate nvarchar(max) AS JSON
)
CROSS APPLY OPENJSON(geoCoordinate)
with (
latitude decimal(5,2) '$.latitude',
longitude decimal(5,2) '$.longitude'
)
To extract more nested structures, they have to be extracted at the top, then parsed with OPENJSON just like geoCoordinate:
要提取更多嵌套结构，必须在顶部提取它们，然后使用OPENJSON解析，就像geoCoordinate一样：
SELECT apMacAddress,associated,band,latitude,longitude ,x,y
FROM OPENJSON(@json, '$.notifications')
with (
apMacAddress nvarchar(500) '$.apMacAddress',
associated bit '$.associated',
band nvarchar(50) '$.band',
geoCoordinate nvarchar(max) AS JSON,
locationCoordinate nvarchar(max) AS JSON
)
CROSS APPLY OPENJSON(geoCoordinate)
with (
latitude decimal(5,2) '$.latitude',
longitude decimal(5,2) '$.longitude'
)
CROSS APPLY OPENJSON(locationCoordinate) with (
x decimal(5,2) '$.x',
y decimal(5,2) '$.y'
)
Will return the locationCoordinate.x and locationCoordinate.y values :
将返回locationCoordinate.x和locationCoordinate.y值：
apMacAddress associated band latitude longitude x y
20:50:7a:09:5t:30 0 IEEE_802_11_A -999.00 -999.00 423.23 23.42
#2
1
In addition to Panagiotis Kanavos reply, suppose you are using SQL Server 2016 and onward (with Compatibility level 130) you can use the OPENJSON function.
除了Panagiotis Kanavos的回复之外，假设您使用的是SQL Server 2016及更高版本（兼容级别130），您可以使用OPENJSON功能。
Another assumption is that you Json structure will not change you can use the following to parse the elements into columns
另一个假设是你的Json结构不会改变你可以使用以下内容将元素解析成列
UPDATE(!!)
UPDATE（!!）
SET NOCOUNT ON
IF OBJECT_ID ('tempdb..##DataSet') IS NOT NULL DROP TABLE ##DataSet
DECLARE @Table TABLE (Id int identity (1,1) , JsonCol NVARCHAR(MAX))
INSERT INTO @Table (JsonCol)
VALUES ('{
"notifications": [
{
"apMacAddress": "20:50:7a:09:5t:30",
"associated": false,
"band": "IEEE_802_11_A",
"confidenceFactor": 192,
"deviceId": "34:f4:23:23:f4:wr",
"entity": "Wired",
"eventId": 2343434,
"floorId": 3244234234234,
"floorRefId": 234235465234,
"geoCoordinate": {
"latitude": -888.0,
"longitude": -888.0,
"unit": "DEGREES"
},
"ipAddress": [
"23.423.23.234"
],
"lastSeen": "2018-02-09T12:24:53.512+0000",
"locationCoordinate": {
"x": 423.2342,
"y": 23.423,
"z": 0.0,
"unit": "FEET"
},
"locationMapHierarchy": "North Campus>North SIde>F4>F4 Academies ",
"manufacturer": "Sony Corporate",
"maxDetectedRssi": {
"antennaIndex": 0,
"apMacAddress": "234:23:56d:45d:5f:20",
"band": "IEEE_802_11_B",
"lastHeardInSeconds": 23,
"rssi": -54,
"slot": 2
},
"notificationType": "locationupdate",
"rawLocation": {
"RawX": 0.0,
"RawY": 0.0,
"Unit": null
},
"ssid": "aide",
"subscriptionName": " Lab Location",
"tagVendorData": null,
"timestamp": 2343657436,
"username": "abc.gmail.com"
}
]
}'),
('{
"notifications": [
{
"apMacAddress": "20:50:7a:09:5t:30",
"associated": false,
"band": "IEEE_802_11_A",
"confidenceFactor": 192,
"deviceId": "34:f4:23:23:f4:wr",
"entity": "Wired",
"eventId": 2343434,
"floorId": 3244234234234,
"floorRefId": 234235465234,
"geoCoordinate": {
"latitude": -999.0,
"longitude": -999.0,
"unit": "DEGREES"
},
"ipAddress": [
"23.423.23.234"
],
"lastSeen": "2018-02-09T12:24:53.512+0000",
"locationCoordinate": {
"x": 423.2342,
"y": 23.423,
"z": 0.0,
"unit": "FEET"
},
"locationMapHierarchy": "North Campus>North SIde>F4>F4 Academies ",
"manufacturer": "Sony Corporate",
"maxDetectedRssi": {
"antennaIndex": 0,
"apMacAddress": "234:23:56d:45d:5f:20",
"band": "IEEE_802_11_B",
"lastHeardInSeconds": 23,
"rssi": -54,
"slot": 2
},
"notificationType": "locationupdate",
"rawLocation": {
"RawX": 0.0,
"RawY": 0.0,
"Unit": null
},
"ssid": "aide",
"subscriptionName": " Lab Location",
"tagVendorData": null,
"timestamp": 2343657436,
"username": "abc.gmail.com"
}
]
}')
,(
'{
"notifications": [
{
"apMacAddress": "20:50:7a:09:5t:30",
"associated": false,
"band": "IEEE_802_11_A",
"confidenceFactor": 192,
"deviceId": "34:f4:23:23:f4:wr",
"entity": "Wired",
"eventId": 2343434,
"floorId": 3244234234234,
"floorRefId": 234235465234,
"geoCoordinate": {
"latitude": -777.0,
"longitude": -777.0,
"unit": "DEGREES"
},
"ipAddress": [
"23.423.23.234"
],
"lastSeen": "2018-02-09T12:24:53.512+0000",
"locationCoordinate": {
"x": 423.2342,
"y": 23.423,
"z": 0.0,
"unit": "FEET"
},
"locationMapHierarchy": "North Campus>North SIde>F4>F4 Academies ",
"manufacturer": "Sony Corporate",
"maxDetectedRssi": {
"antennaIndex": 0,
"apMacAddress": "234:23:56d:45d:5f:20",
"band": "IEEE_802_11_B",
"lastHeardInSeconds": 23,
"rssi": -54,
"slot": 2
},
"notificationType": "locationupdate",
"rawLocation": {
"RawX": 0.0,
"RawY": 0.0,
"Unit": null
},
"ssid": "aide",
"subscriptionName": " Lab Location",
"tagVendorData": null,
"timestamp": 2343657436,
"username": "abc.gmail.com"
}
]
}'
)
;WITH BrkJson as
(
SELECT *
FROM @Table
CROSS APPLY OPENJSON (JsonCol, '$.notifications[0]') R
)
,Dataset as
(
SELECT Id, [key] , [value]
FROM BrkJson
WHERE [type] != 5 /*Array*/
UNION ALL
SELECT b.Id ,b.[Key] +'_'+ t.[key] [key] , t.[value]
FROM BrkJson b
CROSS APPLY OPENJSON (value) t
WHERE b.[type] = 5
)
SELECT *
INTO ##DataSet
FROM Dataset
DECLARE @cols NVARCHAR(MAX)=''
DECLARE @pvt NVARCHAR(MAX)
SELECT @cols +=','+ QUOTENAME([key])
FROM ##DataSet
GROUP BY [key]
SET @cols = STUFF(@cols,1,1,'')
--PRINT @cols
SET @pvt =
'SELECT *
FROM ##DataSet
PIVOT
(
MAX([value]) FOR [key] in ('+@cols+')
) e
'
EXEC sp_executesql @Pvt
DROP TABLE ##DataSet