November 9, 2012 at 9:04 am
I have a piece of code and i am trying to put in Order by, but unsuccessfully. I understand why I can't, but I wonder if other solutions are possible.
SELECT @pivot_columns = isnull(@pivot_columns + ', ', '') + '[' + MilestoneName + ']'
FROM (SELECT DISTINCT MilestoneName FROM #t2 Order by ID) as T
Basically I need to select from #t2 table into the @pivot_columns in the order of ID.
Thanks for help
November 9, 2012 at 9:56 am
You can't have an order by in a subquery like that. You need to include the ID column in your subquery and then use the Order by on the main query.
Like this:
SELECT @pivot_columns = isnull(@pivot_columns + ', ', '') + '[' + MilestoneName + ']'
FROM (SELECT DISTINCT MilestoneName, ID FROM #t2 ) as T
order by t.ID
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
November 9, 2012 at 11:05 am
Thank you for the answer!
This works great but I came across an issue.
This is the sturcture of #t2 (temp table)
CREATE TABLE #t2
( ID INT IDENTITY(1, 1) primary key,
DocID int,
MilestoneDate date,
MilestoneName varchar(500)
)
So in the table I can have more then once same MilestonaName. Since I am selecting now ID along with MilestoneName it gives me more then one record with the same MilestoneName.
This creates a problem in my Pivot code. It gives the error that the column was specified multiple times. This is the entire code I have:
DECLARE @pivot_columns nvarchar(max),
@stmt nvarchar(max)
INSERT INTO #t2
SELECT td.DocID, td.MilestoneDate,
td.MilestoneName
FROM #TempDoc_DocMilestones as td
SELECT @pivot_columns = isnull(@pivot_columns + ', ', '') + '[' + MilestoneName + ']'
FROM (SELECT DISTINCT MilestoneName,ID FROM #t2) as T Order by #t2.ID
SELECT @stmt = ' SELECT * FROM #t2 as t
PIVOT
( MIN(MilestoneDate)
FOR MilestoneName IN (' + @pivot_columns + ')
) as PT'
EXEC sp_executesql
@stmt = @stmt
DROP TABLE dbo.#TempDoc_DocMilestones
DROP TABLE dbo.#t2
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply