Author |
Topic |
chriztoph
Posting Yak Master
184 Posts |
Posted - 2012-12-21 : 03:11:35
|
Can someone fix this:SELECT * FROM (SELECT reference, plateno FROM truck_booking_allocation WHERE plateno IS NOT NULL OR plateno <> '') TruckPlates PIVOT(COUNT(plateno) FOR plateno IN (STUFF((SELECT '],[' + plateno FROM truck_booking_allocation WHERE plateno IS NOT NULL AND LTRIM(plateno) <> '' GROUP BY plateno ORDER BY '],[' + CONVERT(VARCHAR(10), COUNT(plateno)) FOR XML PATH(''), 1, 2, '')) + ']')) AS PivotTableThanks in advance.. |
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
|
chriztoph
Posting Yak Master
184 Posts |
Posted - 2012-12-21 : 03:37:22
|
DECLARE @columns VARCHAR(MAX)DECLARE @convert VARCHAR(MAX)SELECT @columns = STUFF((SELECT '],[' + plateno FROM truck_booking_allocation WHERE plateno IS NOT NULL AND LTRIM(plateno) <> '' GROUP BY plateno ORDER BY '],[' + CONVERT(VARCHAR(10), COUNT(plateno)) FOR XML PATH('')), 1, 2, '') + ']'SET @convert = 'SELECT * FROM (SELECT reference, plateno FROM truck_booking_allocation WHERE plateno IS NOT NULL OR plateno <> '''') TruckPlates PIVOT(COUNT(plateno) FOR plateno IN (' + @columns + ')) AS PivotTable'EXEC (@convert)I have this one and it works but I want to save it as view. how do I do that? |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2012-12-21 : 03:44:08
|
you cant save this as a view. Why not make it as a procedure instead?------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
chriztoph
Posting Yak Master
184 Posts |
Posted - 2012-12-21 : 04:05:13
|
This is just my reference and I need to join it in my main table so that is why I want to make it as view. |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2012-12-21 : 04:12:16
|
ok then populate a temporary table with this dynamic query (use insert...exec or sp_executesql). then use that table to join to main table------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
chriztoph
Posting Yak Master
184 Posts |
Posted - 2012-12-21 : 04:16:59
|
I'm sorry but how do i do that? the plateno is dynamic, it can grow. |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2012-12-21 : 04:22:58
|
you can create a table on the fly using SELECT...INTOdo something likeIF OBJECT_ID('tempdb..#YourTable') IS NOT NULL DROP TABLE #YourTableDECLARE @columns VARCHAR(MAX)DECLARE @convert VARCHAR(MAX)SELECT @columns = STUFF((SELECT '],[' + platenoFROM truck_booking_allocationWHERE plateno IS NOT NULL AND LTRIM(plateno) <> ''GROUP BY platenoORDER BY '],[' + CONVERT(VARCHAR(10), COUNT(plateno))FOR XML PATH('')), 1, 2, '') + ']'SET @convert = 'SELECT * INTO #YourTable FROM (SELECT reference, plateno FROM truck_booking_allocation WHERE plateno IS NOT NULL OR plateno <> '''') TruckPlatesPIVOT(COUNT(plateno) FOR platenoIN (' + @columns + ')) AS PivotTable'EXEC (@convert) ------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
|