SQL SERVER 2008 Pivot

Question

SQL SERVER 2008 Pivot

Viewing 7 posts - 31 through 36 (of 36 total)

You must be logged in to reply to this topic. Login to reply

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 1

Why do you still include the local temporary table #sipDimensionPos in your solution? If you were creating it as a filtered subset of the table from which it is derived (i.e. with a WHERE clause), then it would make sense, but there's no filter...

Does it run?

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

datelligence Say Hey Kid Points: 672 More actions · Answer 2

Ok I´m a little bit lost. So the temporary table was created just for say, the problem resolution, something didactic. Your rigth there is no filtering a subset of the table. Again plese check this, I removed the whole bunch of temporary table.

--===== Create a table variable to store the id_CliPadre

DECLARE @PivotCliPadres VARCHAR(1000)

SELECT @PivotCliPadres = COALESCE(

@PivotCliPadres + ',[' + cast(id_CliPadre as varchar) + ']',

'[' + cast(id_CliPadre as varchar)+ ']')

FROM (

SELECT id_CliPadre

FROM dbo.sipDimensionPos

GROUP BY id_CliPadre

) d

ORDER BY d.id_CliPadre

-- check

--SELECT @PivotCliPadres

--===== Execute the pivot with a table variable

DECLARE @PivotTableSQL NVARCHAR(MAX)

SET @PivotTableSQL = N'

SELECT *

FROM (select codigo_item, item, round(net_price,1) as net_price, id_CliPadre, id_stock from dbo.sipFTdataStage INNER JOIN

dbo.sipDimensionPos ON dbo.sipFTdataStage.id_cliente = dbo.sipDimensionPos.id_cliente) tabla

pivot (

sum(net_price) for id_CliPadre IN (

' + @PivotCliPadres + '

)

) pivotable

,

print (@PivotTableSQL)

EXECUTE(@PivotTableSQL)'

datelligence Say Hey Kid Points: 672 More actions · Answer 3

However once executed I receive this message:

"Command(s) completed successfully."

How do I query the pivoted data?

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 4

You have a closing quotation mark in the wrong place

,

print (@PivotTableSQL)

EXECUTE(@PivotTableSQL)' -- <-- closing quotation mark in the wrong place

which means that the PRINT statement and the EXECUTE statement are appended to the string @PivotTableSQL, instead of being executed. Put it back in the right place, which you can find from an earlier post where you successfully ran the dynamic sql query.

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

datelligence Say Hey Kid Points: 672 More actions · Answer 5

Yes, you´re great, it works, I get a pivoted datagrid. Thanks a lot. How do I award you. I´m grateful with you!!

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 6

A squash from your nice terracotta tiled roof 😉

Top work, Jorge. It's good to work with a poster who's prepared to put in some effort, as you have always.

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

datelligence Say Hey Kid Points: 672 More actions · Answer 7

Thank´s Chris, really learned and enjoyed. Thanks for asking me to give a little bit and not just ask for the solution.