| Author |
 Topic  |
|
|
chriztoph
Posting Yak Master
Philippines
155 Posts |
 Posted - 12/21/2012 : 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 PivotTable
Thanks in advance.. |
|
|
visakh16
Very Important crosS Applying yaK Herder
India
47081 Posts |
|
|
chriztoph
Posting Yak Master
Philippines
155 Posts |
Posted - 12/21/2012 : 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
India
47081 Posts |
Posted - 12/21/2012 : 03:44:08
|
you cant save this as a view. Why not make it as a procedure instead?
------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
|
|
|
|
chriztoph
Posting Yak Master
Philippines
155 Posts |
Posted - 12/21/2012 : 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
India
47081 Posts |
Posted - 12/21/2012 : 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 MVP
http://visakhm.blogspot.com/
|
|
|
|
chriztoph
Posting Yak Master
Philippines
155 Posts |
Posted - 12/21/2012 : 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
India
47081 Posts |
Posted - 12/21/2012 : 04:22:58
|
you can create a table on the fly using SELECT...INTO
do something like
IF OBJECT_ID('tempdb..#YourTable') IS NOT NULL
DROP TABLE #YourTable
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 * INTO #YourTable 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)
------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
|
|
|
| |
Topic |
|
Pivot
