November 22, 2011 at 2:12 am
I have a table in my companies database that looks like this (I can not change this structure).
CREATE TABLE #TemplateDictionary(
[TMPL_OBJECT_NAME] [nvarchar](100) NOT NULL,
[FieldName] [nvarchar](100) NOT NULL,
[FieldDataType] [nvarchar](100) NOT NULL,
[FieldDataTypeLength] [nvarchar](20) NOT NULL,
[FieldPosition] INT NOT NULL)
GO
INSERT INTO #TemplateDictionary VALUES('CustomerAddress','Address1','CHAR','50',1)
INSERT INTO #TemplateDictionary VALUES('CustomerAddress','Address2','CHAR','50',2)
INSERT INTO #TemplateDictionary VALUES('CustomerAddress','Address3','CHAR','50',3)
INSERT INTO #TemplateDictionary VALUES('CustomerAddress','Postcode','CHAR','20',4)
SELECT *
FROM #TemplateDictionary
--DROP TABLE #TemplateDictionary
I want to take the data from this table and transpose it so that it can be extracted and given to the user that needs this data in the format below:
Address1|Address2|Address3|Postcode
CHAR|CHAR|CHAR|CHAR
50|50|50|50
I have tried a few techniques but am not really sure how to do this, we are using SQL 2008 R2.
Any help or pointers would be greatly appreciated.
November 22, 2011 at 3:25 am
This is probably not the best way to go about it, I'm not good with PIVOT/UNPIVOT because I feel that this sort of job is better done in the presentation layer rather than the database.
But this does get the results you're after.
SELECT MAX([1]) AS [1], MAX([2]) AS [2], MAX([3]) AS [3], MAX([4]) AS [4]
FROM (SELECT [1],[2],[3],[4], 1 AS OrderBy
FROM #TemplateDictionary a
PIVOT (MAX([FieldName]) FOR [FieldPosition] IN ([1],[2],[3],[4]) ) AS pvt
UNION ALL
SELECT [1],[2],[3],[4], 2
FROM #TemplateDictionary a
PIVOT (MAX([FieldDataType]) FOR [FieldPosition] IN ([1],[2],[3],[4]) ) AS pvt
UNION ALL
SELECT [1],[2],[3],[4], 3
FROM #TemplateDictionary a
PIVOT (MAX([FieldDataTypeLength]) FOR [FieldPosition] IN ([1],[2],[3],[4]) ) AS pvt ) a
GROUP BY OrderBy
ORDER BY OrderBy
November 22, 2011 at 3:41 am
Thanks Cadavre - that produces exactly the results I asked for, but I guess I maybe simplified my question too much.
The problem I have is that No. of rows is dynamic, so whilst 'CustomerAddress' might have 4 rows/records, 'CustomerInvoice' might have 10 rows/records.
I will have multiple TMPL_OBJECT_NAME in the same table all with different amount of rows, so I need to find a way to make this dynamic (or as you have suggested - try and do it in the presentation layer)
November 22, 2011 at 3:46 am
squidder11 (11/22/2011)
Thanks Cadavre - that produces exactly the results I asked for, but I guess I maybe simplified my question too much.The problem I have is that No. of rows is dynamic, so whilst 'CustomerAddress' might have 4 rows/records, 'CustomerInvoice' might have 10 rows/records.
I will have multiple TMPL_OBJECT_NAME in the same table all with different amount of rows, so I need to find a way to make this dynamic (or as you have suggested - try and do it in the presentation layer)
Right, try Dynamic Cross Tabs part 1[/url] and part 2[/url] by Jeff Moden[/url].
Have a go yourself, if you get stuck then post back and either I'll give you a hand or someone else will 😉
November 22, 2011 at 5:35 am
Cadavre,
I have read both the articles you posted and have attempted to apply this to my solution but with no luck as yet.
So if you could offer some more help that would be great.
November 22, 2011 at 5:51 am
This is what I have so far, how do I get more than one row of data in the results:
CREATE TABLE #TemplateDictionary(
[TMPL_OBJECT_NAME] [nvarchar](100) NOT NULL,
[FieldName] [nvarchar](100) NOT NULL,
[FieldDataType] [nvarchar](100) NOT NULL,
[FieldDataTypeLength] [nvarchar](20) NOT NULL,
[FieldPosition] INT NOT NULL)
GO
INSERT INTO #TemplateDictionary VALUES('CustomerAddress','Address1','CHAR','50',1)
INSERT INTO #TemplateDictionary VALUES('CustomerAddress','Address2','CHAR','50',2)
INSERT INTO #TemplateDictionary VALUES('CustomerAddress','Address3','CHAR','50',3)
INSERT INTO #TemplateDictionary VALUES('CustomerAddress','Postcode','CHAR','20',4)
SELECT *
FROM #TemplateDictionary
--------------------------------------------------------------
--------------------------------------------------------------
--Code to Transpose data
DECLARE @columns NVARCHAR(4000)
SELECT @columns = COALESCE(@columns + ',[' + cast(FieldName as varchar) + ']',
'[' + cast(FieldName as varchar)+ ']')
FROM #TemplateDictionary
GROUP BY [FieldName]
PRINT @columns
DECLARE @query NVARCHAR(4000)
SET @query = N'
select * from
(SELECT
s.FieldName as [AssetName]
, s.[FieldDataType] AS [FDT]
FROM #TemplateDictionary as s
) DataTable
PIVOT
(
MAX([FDT])
FOR AssetName
IN (' + @columns + ')
)
AS p'
EXEC sp_executesql @query
--------------------------------------------------------------
--------------------------------------------------------------
DROP TABLE #TemplateDictionary
November 22, 2011 at 6:26 am
squidder11 (11/22/2011)
This is what I have so far, how do I get more than one row of data in the results
BEGIN TRAN
SET NOCOUNT ON
CREATE TABLE #TemplateDictionary(
[TMPL_OBJECT_NAME] [nvarchar](100) NOT NULL,
[FieldName] [nvarchar](100) NOT NULL,
[FieldDataType] [nvarchar](100) NOT NULL,
[FieldDataTypeLength] [nvarchar](20) NOT NULL,
[FieldPosition] INT NOT NULL)
GO
INSERT INTO #TemplateDictionary VALUES('CustomerAddress','Address1','CHAR','50',1)
INSERT INTO #TemplateDictionary VALUES('CustomerAddress','Address2','CHAR','50',2)
INSERT INTO #TemplateDictionary VALUES('CustomerAddress','Address3','CHAR','50',3)
INSERT INTO #TemplateDictionary VALUES('CustomerAddress','Postcode','CHAR','20',4)
DECLARE @columns NVARCHAR(4000)
SELECT @columns = COALESCE(@columns + ',[' + CAST(FieldName AS VARCHAR) + ']', '[' + CAST(FieldName AS VARCHAR) + ']')
FROM #TemplateDictionary
GROUP BY [FieldName]
DECLARE @query NVARCHAR(4000)
SET @query = N'
SELECT *
FROM (SELECT s.FieldName AS [AssetName],
s.[FieldDataType] AS [FDT]
FROM #TemplateDictionary s) DataTable
PIVOT (MAX([FDT]) FOR AssetName IN (' + @columns + ') ) AS p
UNION ALL
SELECT *
FROM (SELECT s.FieldName AS [AssetName],
s.[FieldDataTypeLength] AS [FDT]
FROM #TemplateDictionary s) DataTable
PIVOT (MAX([FDT]) FOR AssetName IN (' + @columns + ') ) AS p'
EXEC sp_executesql @query
ROLLBACK
Returns: -
Address1 Address2 Address3 Postcode
---------------------------------------------------------------------------------------------------- ---------------------------------------------------------------------------------------------------- ---------------------------------------------------------------------------------------------------- ----------------------------------------------------------------------------------------------------
CHAR CHAR CHAR CHAR
50 50 50 20
November 22, 2011 at 6:36 am
Cadavre - Thank you for all your help
November 22, 2011 at 6:51 am
No problem. Hell, if you're really lucky then one of the others that knows a bit more about PIVOT will happen by with a better solution 😀
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply