Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- RESOURCE | DESCRIPTION | VALUE
- Test A Name | Resource A-xyz
- Test A | Height | 20
- Test A | Unit | ft
- Test A | Location | Site 1
- Test B | Volume | 30
- Test C | Width | 10
- Test C | Unit | in
- RESOURCE | Name | Height | Unit | Location | Volume | Width
- Test A | Resource A-xyz | 20 | ft | Site 1 | |
- Test B | | | | | 30 |
- Test C | | | in | | | 10
- SELECT *
- FROM Table1
- PIVOT(MAX(VALUE) FOR DESCRIPTION IN (Name,Height,Unit,Location,Volume,Width))p
- SELECT resource,Name,Height,Unit,Location,Volume,Width
- FROM
- #T1 AS SourceTable
- PIVOT
- (
- max(value)
- FOR description IN ([Name],[Height],[Unit],[Location],[Volume],[Width])
- ) AS PivotTable
- ORDER BY 1
- CREATE TABLE demo
- (
- RESOURCE VARCHAR(100),
- DESCRIPTION VARCHAR(100), VALUE VARCHAR(100)
- )
- INSERT INTO demo VALUES
- ('Test A' , 'Name' , 'Resource A-xyz')
- ,('Test A' , 'Height' , '20')
- ,('Test A' , 'Unit' , 'ft')
- ,('Test A' , 'Location' , 'Site 1')
- ,('Test B' , 'Volume' , '30')
- ,('Test C' , 'Width' , '10')
- ,('Test C' , 'Unit' , 'in')
- SELECT DISTINCT DESCRIPTION INTO #tbl FROM demo
- //Get list of values to be pivoted
- DECLARE @var NVARCHAR(1000)=''
- SELECT @var = @var +', ' + DESCRIPTION FROM #tbl
- SELECT @var = SUBSTRING(@var, 2, LEN(@var))
- SELECT @var
- DECLARE @query NVARCHAR(2000) = 'SELECT * FROM demo PIVOT(MAX(VALUE) FOR DESCRIPTION IN ('+ @var + '))p'
- EXEC sp_executesql @query
- CREATE TABLE #tb1
- (
- [RESOURCE] varchar(100),
- [FIELD1] varchar(100),
- [FIELD2] varchar(50),
- .
- .
- .
- [LAST FIELD] varchar(50),
- )
- INSERT INTO #tb1 (RESOURCE)
- SELECT DISTINCT RESOURCE FROM tb2
- ORDER BY Resource ASC
- UPDATE #tb1 SET [FIELD1] = (SELECT VALUE FROM tb2 WHERE Resource = #tb1.Resource and Property = [FIELD1])
- .
- .
- .
- UPDATE #tb1 SET [LAST FIELD] = (SELECT VALUE FROM tb2 WHERE Resource = #tb1.Resource and Property = [LAST FIELD])
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement