SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Pivot
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

chriztoph
Posting Yak Master

Philippines
184 Posts

Posted - 12/21/2012 :  03:11:35  Show Profile  Reply with Quote
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
52325 Posts

Posted - 12/21/2012 :  03:22:49  Show Profile  Reply with Quote
need to do it in two steps

see
http://beyondrelational.com/modules/2/blogs/70/posts/10840/dynamic-pivot-in-sql-server-2005.aspx

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page

chriztoph
Posting Yak Master

Philippines
184 Posts

Posted - 12/21/2012 :  03:37:22  Show Profile  Reply with Quote
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?
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52325 Posts

Posted - 12/21/2012 :  03:44:08  Show Profile  Reply with Quote
you cant save this as a view. Why not make it as a procedure instead?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page

chriztoph
Posting Yak Master

Philippines
184 Posts

Posted - 12/21/2012 :  04:05:13  Show Profile  Reply with Quote
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.
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52325 Posts

Posted - 12/21/2012 :  04:12:16  Show Profile  Reply with Quote
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/

Go to Top of Page

chriztoph
Posting Yak Master

Philippines
184 Posts

Posted - 12/21/2012 :  04:16:59  Show Profile  Reply with Quote
I'm sorry but how do i do that? the plateno is dynamic, it can grow.
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52325 Posts

Posted - 12/21/2012 :  04:22:58  Show Profile  Reply with Quote
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/

Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.12 seconds. Powered By: Snitz Forums 2000