October 23, 2011 at 2:24 pm
Hello
I have a table with the following data
00001315, 10a
00001315, 1a
00001315, 10b
00001315, 6
00001315, 2b
I need to be able to transform the data to one row
00001315, 10a,1a,10b,6,2b
I have had a look at the PIVOT function but am not able to get it to work
Can anyone please offer a solution
SQL Server 2008r2/2008/2005
Many Thanks
October 23, 2011 at 5:32 pm
If you truly mean to convert row to "columns" as you say...
http://qa.sqlservercentral.com/articles/t-sql/63681/
If you actually meant convert the rows to a single, comma delimited column as you've shown...
http://qa.sqlservercentral.com/articles/comma+separated+list/71700/
Last but not least, GOOGLE is your friend. 🙂
If you'd like a coded example for your specific data, please provide coded data. See the first link in my signature below for how to do that.
--Jeff Moden
October 23, 2011 at 6:01 pm
Thank you for your reply 🙂
I have tried the pivot table approach but neither column lends itself to an aggregate function and unfortunatly my skill set with SQL is limited
SELECT 'BAForeignKey' BA, CAST([0] AS VARCHAR(50)) AS BCAClass1 ,[1] AS BCAClass2,[2] AS bcaclass3,[3] AS bcaclass4,[4] AS bcaclass5
FROM
( SELECT cast(BAForeignKey AS varchar(50))AS BAForeignKey, BldClass FROM ApplicationBldTypes WHERE BAForeignKey = '00001315') AS p
PIVOT
( count(BAForeignKey)
FOR BldClass IN ([0],[1],[2],[3],[4])
) AS pvt
this query just returns
BAForeignKey,0 ,0,0,0,0
The select case approach is complex as the number of variations in build class (2nd column) can be > 100
Actually I only require 5 columns of data even if my source table has 25 rows, the first column of my source data will always be the same
I undertsand a little about using dynamic SQL to only retieve the distinct bldClass from the source and then use a case statement
but then....
Many Thanks
October 23, 2011 at 8:51 pm
andre-394971 (10/23/2011)
I have tried the pivot table approach but neither column lends itself to an aggregate function and unfortunatly my skill set with SQL is limited
Heh... use MAX instead... it's an aggregate function, as well. 😉 Trust me... it works.
Again, if you want a coded answer, please see the article at the first link in my signature below for how to post the data in a readily consumable format. 😉
--Jeff Moden
October 25, 2011 at 3:01 pm
Back again I have coded my question as suggested 🙂
IF OBJECT_ID('TempDB..#mytable','U') IS NOT NULL
DROP TABLE #mytable
CREATE TABLE #mytable
( ID INT IDENTITY(1,1) PRIMARY KEY CLUSTERED,
BARef VARCHAR(50),
BLDClass VARCHAR(50)
)
INSERT INTO #mytable
(BAREf,BLDClass)
SELECT '00001315','10a' UNION all
SELECT '00001315','1a' UNION all
SELECT '00001315','10b' UNION all
SELECT '00001315','1b' UNION all
SELECT '00001315','9a'
SELECT * FROM #mytable
SELECT 'BARef', CAST([0] AS VARCHAR(50)) AS BCAClass1 ,[1] AS BCAClass2,[2] AS bcaclass3,[3] AS bcaclass4,[4] AS bcaclass5
FROM
( SELECT BARef, BldClass FROM #mytable WHERE BARef = '00001315') AS p
PIVOT
( max(p.BARef)
FOR BldClass IN ([0],[1],[2],[3],[4])
) AS pvt
I end up with
BARef,Null,Null,Null,Null,Null
Can anyone offer tell how to get
00001315,10a,1a,10b,1b,9a
The order in which I have shown the result is important
I will only ever require 6 columns regardless of how many records are returned from the table
Many Thanks
October 25, 2011 at 4:43 pm
I added to your data (below) so you can see this actually works...
IF OBJECT_ID('TempDB..#mytable','U') IS NOT NULL
DROP TABLE #mytable
CREATE TABLE #mytable
( ID INT IDENTITY(1,1) PRIMARY KEY CLUSTERED,
BARef VARCHAR(50),
BLDClass VARCHAR(50)
)
INSERT INTO #mytable
(BAREf,BLDClass)
SELECT '00001315','10a' UNION all
SELECT '00001316','abc' UNION all
SELECT '00001315','1a' UNION all
SELECT '00001316','456' UNION all
SELECT '00001315','10b' UNION all
SELECT '00001316','xyz' UNION all
SELECT '00001315','1b' UNION all
SELECT '00001316','123' UNION all
SELECT '00001316','01a' UNION ALL
SELECT '00001315','9a'
SELECT * FROM #mytable
And, like I said, use MAX... and the data in each row is in order according to the value stored in the ID column...
WITH
cteEnumerate AS
(
SELECT RowNum = ROW_NUMBER() OVER (PARTITION BY BAREf ORDER BY ID),
BAREf,
BLDClass
FROM #mytable
)
SELECT BAREf,
BCAClass1 = MAX(CASE WHEN RowNum = 1 THEN BLDClass ELSE '' END),
BCAClass2 = MAX(CASE WHEN RowNum = 2 THEN BLDClass ELSE '' END),
BCAClass3 = MAX(CASE WHEN RowNum = 3 THEN BLDClass ELSE '' END),
BCAClass4 = MAX(CASE WHEN RowNum = 4 THEN BLDClass ELSE '' END),
BCAClass5 = MAX(CASE WHEN RowNum = 5 THEN BLDClass ELSE '' END)
FROM cteEnumerate
GROUP BY BAREf
ORDER BY BAREf
;
--Jeff Moden
October 25, 2011 at 5:08 pm
Ok Thanks Ill Give your suggestion a try
Finally got the pivot to work 🙂
Im puzzled as too why the following is happening
This works...
There are only three records returned from dbo.ApplicationBldTypes
IF OBJECT_ID('TempDB..#mytable','U') IS NOT NULL
DROP TABLE #mytable
CREATE TABLE #mytable
( RowId INT IDENTITY(1,1) PRIMARY KEY CLUSTERED,
BAForeignKey VARCHAR(8),
BLDClass VARCHAR(50)
)
INSERT INTO #mytable
SELECT BAForeignKey, BldClass FROM dbo.ApplicationBldTypes WHERE BAForeignKey = '00001315'
SELECT [0] AS BCAClass1 ,[1] AS BCAClass2,[2] AS bcaclass3,[3] AS bcaclass4,[4] AS bcaclass5,[5] AS bcaclass6
FROM
( SELECT TOP(5) RowId, BldClass FROM #mytable WHERE BAForeignKey = '00001315' ORDER BY RowId) AS p
PIVOT
( max(p.BldClass)
FOR RowId IN ([0],[1],[2],[3],[4],[5])
) AS pvt
Gives Me
BCAClass1 BCAClass2 bcaclass3 bcaclass4 bcaclass5 bcaclass6
NULL 1a 10b 10a Null Null
Which is OK
BUT THIS Does NOT...the difference is Im actually using my live database eg #MyTable is replaced by dbo.ApplicationBldTypes
SELECT [0] AS BCAClass1 ,[1] AS BCAClass2,[2] AS bcaclass3,[3] AS bcaclass4,[4] AS bcaclass5,[5] AS bcaclass6
FROM
( SELECT TOP(5) RowId, BldClass FROM dbo.ApplicationBldTypes WHERE BAForeignKey = '00001315' ORDER BY RowId ) AS p
PIVOT
( max(p.BldClass)
FOR RowId IN ([0],[1],[2],[3],[4],[5])
) AS pvt
Gives Me
BCAClass1 BCAClass2 bcaclass3 bcaclass4 bcaclass5 bcaclass6
NULL NULL NULL NULL NULL NULL
October 25, 2011 at 5:26 pm
Hello Again
I have tried your suggetion on the live database and it works...many thanks....:-)
Now I need to be able to incorperate the result row returned by your suggestion into a result row from a normal query.
EG The normal query, which will only ever return one row, goes like this
Select Name,Address,Postcode..etc and then I need to add the five columns returned from your suggestion
From ......
Where BAForeignKey = '00001315'
Not sure what the best appoach would be ?
Many thanks
October 25, 2011 at 5:51 pm
Ummmm... none of this is going to work correctly unless the dbo.ApplicationBldTypes has either an IDENTITY column or a "DateCreated" column (of some sort) to preserve the order of the individual elements on each row in the result set. Does the dbo.ApplicationBldTypes table have such a thing?
If not, are you sure that the order of the 5 columns of data actually matters?
--Jeff Moden
October 25, 2011 at 6:13 pm
Sorry That was remiss of me the ApplicationBuildingTypes table has a RowId (int) column which is the primary key
October 25, 2011 at 6:27 pm
Then you don't even need a Temp Table. I'll be back in a bit.
--Jeff Moden
October 25, 2011 at 7:45 pm
Sorry for the delay... there were a couple of posts ahead of you...
--Pretend that this is your dbo.ApplicationBldTypes table...
IF OBJECT_ID('TempDB..#ApplicationBldTypes','U') IS NOT NULL
DROP TABLE #ApplicationBldTypes
;
CREATE TABLE #ApplicationBldTypes
(
RowId INT IDENTITY(1,1) PRIMARY KEY CLUSTERED,
BAForeignKey VARCHAR(8),
BLDClass VARCHAR(50)
)
;
INSERT INTO #ApplicationBldTypes
(BAForeignKey, BLDClass)
SELECT '00001315','10a' UNION all
SELECT '00001316','abc' UNION all
SELECT '00001315','1a' UNION all
SELECT '00001316','456' UNION all
SELECT '00001315','10b' UNION all
SELECT '00001316','xyz' UNION all
SELECT '00001315','1b' UNION all
SELECT '00001316','123' UNION all
SELECT '00001316','01a' UNION ALL
SELECT '00001315','9a'
;
SELECT * FROM #ApplicationBldTypes
;
I used a Temp table as a substitute for your table. Change #ApplicationBldTypes to dbo.ApplicationBldTypes and you should be good to go depending on your indexes...
--===== This is exactly the same code I wrote before except we're not reading
-- from a Temp Table and I've added the WHERE clause you wanted.
WITH
cteEnumerate AS
(
SELECT RowNum = ROW_NUMBER() OVER (PARTITION BY BAForeignKey ORDER BY RowID),
BAForeignKey,
BLDClass
FROM #ApplicationBldTypes
WHERE BAForeignKey = '00001315' --<<< LOOK HERE! Could be a variable in a proc!!!
)
SELECT BAForeignKey,
BCAClass1 = MAX(CASE WHEN RowNum = 1 THEN BLDClass ELSE '' END),
BCAClass2 = MAX(CASE WHEN RowNum = 2 THEN BLDClass ELSE '' END),
BCAClass3 = MAX(CASE WHEN RowNum = 3 THEN BLDClass ELSE '' END),
BCAClass4 = MAX(CASE WHEN RowNum = 4 THEN BLDClass ELSE '' END),
BCAClass5 = MAX(CASE WHEN RowNum = 5 THEN BLDClass ELSE '' END)
FROM cteEnumerate
GROUP BY BAForeignKey
-- ORDER BY BAForeignKey -- Don't need this because you're using RBAR.
;
--Jeff Moden
October 26, 2011 at 3:33 pm
Hi There
Thanks you so much for your help 🙂
Just one last question
Is it possible to save this query as a view?
Andre
October 26, 2011 at 4:39 pm
Sure... you'll probably need to remove the WHERE clause in the first CTE. You can use such a WHERE clause when you select from the view. Be well advised that if you put any of the 5 calculated columns in an external WHERE clause, the whole view will need to resolve for the entire table before it can make such a decision.
With that in mind and if you really need the code to return just one row at a time, you might want to consider turning it into a real "Inline Table Valued Function" just to prevent people the temptation of filtering on an aggregated column from the view.
--Jeff Moden
October 26, 2011 at 4:57 pm
HI Again
Thanks for that...not exactly sure I understood what you said...still new to the real power of SQL.
I have decided to create a stored proceedure and access that from the application.
Once again Thanks for your help 😀
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply