SQL SERVER 2008 Pivot

Question

Post reply

SQL SERVER 2008 Pivot

datelligence

Say Hey Kid

Points: 672
More actions
January 19, 2010 at 6:40 pm

#386141

Hi, I´m working with pivot to dinamically generate dimensional reports based on users choice. Unfortunately can´t solve this issue: here is the query:
===================================================
DECLARE @PivotCliPadres VARCHAR(50)
SELECT @PivotCliPadres =
COALESCE(
@PivotCliPadres + ',[' + cast(id_CliPadre as varchar) + ']',
'[' + cast(id_CliPadre as varchar)+ ']'
)
FROM dbo.sipDimensionPos
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
,
EXECUTE(@PivotTableSQL)
=======================================
when executed I receive this message
"Msg 105, Level 15, State 1, Line 17
Unclosed quotation mark after the character string '
)
) pivotable
,
EXECUTE(@PivotTableSQL)
'.
Msg 102, Level 15, State 1, Line 17
Incorrect syntax near '
)
) pivotable
,
EXECUTE(@PivotTableSQL)
'."
Any advice is well received, thanks in advance.

Viewing 15 posts - 1 through 15 (of 36 total)

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

Jeff Moden SSC Guru Points: 1004356 More actions · Answer 1

Change your EXECUTE to a PRINT and let's see what you get...

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

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

Hi Jeff, I´done it and get the same message

"Msg 105, Level 15, State 1, Line 17

Unclosed quotation mark after the character string '

)

) pivotable

,

PRINT (@PivotTableSQL)

'.

Msg 102, Level 15, State 1, Line 17

Incorrect syntax near '

)

) pivotable

,

PRINT (@PivotTableSQL)"

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

Your character string - your query - is missing the closing quotation mark.

^{“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 4

Thanks Chris, I have a "freshman" question. The stored procedure is generating a table variable. When adding the missing quotation mark I get the successfully executed command message. But when trying to get the data grid with the resolved data, I get lost. Before closing the execution plan I added a query to select * from @PivotTableSQL, but again don´t get the data grid, just a success message?

Thanks in advance

Jorge

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

Can you post the whole stored procedure Jorge?

^{“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 6

Thanks Chris:

First of all maybe I´m wrong and this is not a stored procedure, is just the creation of a table variable. Here is what I´m doing: I´m queryng a fact table to generate a crosstab report, say PIVOT, where the user, a web user, selects two or three dimensions to query and get a customized report, say a datagrid deployed over user´s webbrowser.

--===== First create a table variable with the dinamically generated data

DECLARE @PivotCliPadres VARCHAR(50)

SELECT @PivotCliPadres =

COALESCE(

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

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

)

FROM dbo.sipDimensionPos

--=====

--=====Second create and execute the stored procedure? or just 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

,

EXECUTE(@PivotTableSQL)

--=====

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

Does this query work? It looks like the closing quote is missing.

In most cases a stored procedure would be recommended - you can pass the necessary parameters and receive a result set.

Post what you have working.

^{“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 8

Ok Chris:

--===== Fact Table from wich I need to query data

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

SET ANSI_PADDING ON

GO

CREATE TABLE [dbo].[sipFTdataStage](

[id_sipOltp] [bigint] NOT NULL,

[id_date_of_survey] [int] NULL,

[fecha] [smalldatetime] NOT NULL,

[mes_numero] [int] NULL,

[mes] [varchar](50) NULL,

[dia_semana] [int] NULL,

[año] [int] NULL,

[dia] [varchar](50) NULL,

[dia_mes] [int] NULL,

[time_of_survey] [time](7) NULL,

[id_survey] [bigint] NULL,

[survey_code] [varchar](75) NULL,

[id_surveyer] [smallint] NULL,

[surveyer_name] [varchar](50) NULL,

[id_customer] [smallint] NULL,

[customer_name] [varchar](50) NULL,

[id_pos] [smallint] NULL,

[id_cliente] [float] NULL,

[establecimiento] [nvarchar](255) NULL,

[id_item] [int] NULL,

[item] [varchar](300) NULL,

[codigo_item] [bigint] NULL,

[tamanno] [varchar](50) NULL,

[id_stock] [tinyint] NULL,

[inventario] [varchar](50) NULL,

[id_currency] [tinyint] NULL,

[moneda] [varchar](25) NULL,

[ordinal] [smallint] NULL,

[initial_price] [money] NULL,

[id_promotion] [tinyint] NULL,

[promocion] [varchar](50) NOT NULL,

[id_promotionSub] [tinyint] NULL,

[promocionSub] [varchar](50) NULL,

[valor_monetario] [money] NULL,

[partial_price] [money] NULL,

[net_price] [money] NULL,

[dataInput_date] [smalldatetime] NULL,

CONSTRAINT [PK_sipFTdataStage] PRIMARY KEY CLUSTERED

(

[id_sipOltp] ASC

)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

) ON [PRIMARY]

GO

SET ANSI_PADDING OFF

GO

--=====

--===== Table where I wish to insert the table variable data to query after table variable execution

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

CREATE TABLE [dbo].[sipTest](

[codigo_item] [bigint] NULL,

[item] [bigint] NULL,

[net_price] [money] NULL,

[id_CliPadre] [int] NULL,

[id_stock] [tinyint] NULL

) ON [PRIMARY]

GO

--=====

--===== Execution of a table variable to get Pivot data

DECLARE @PivotCliPadres VARCHAR(50)

SELECT @PivotCliPadres =

COALESCE(

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

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

)

FROM dbo.sipDimensionPos

DECLARE @PivotTableSQL NVARCHAR(MAX)

SET @PivotTableSQL = N'

insert into dbo.sipTest (codigo_item, item, round(net_price,1) as net_price, id_CliPadre, id_stock) 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

,

EXECUTE(@PivotTableSQL)'

--=====

--===== At this point I need to query the "pivoted" data

--=====

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

Forget about the INSERT for the moment - does the pivot work yet?

^{“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 10

Ok, once executed the following statement

--=====

DECLARE @PivotCliPadres VARCHAR(50)

SELECT @PivotCliPadres =

COALESCE(

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

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

)

FROM dbo.sipDimensionPos

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

,

EXECUTE(@PivotTableSQL)'http://www.sqlservercentral.com/Forums/Skins/Classic/Images/RichTextBoxTable/tbl_bottomleft.gif

--=====

get the following message

--===== Message after execution

Command(s) completed successfully.

--=====

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

Excuse me, some strings were added and don´t belong to the query: I repeat

Ok, once executed the following statement

--=====

DECLARE @PivotCliPadres VARCHAR(50)

SELECT @PivotCliPadres =

COALESCE(

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

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

)

FROM dbo.sipDimensionPos

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

,

EXECUTE(@PivotTableSQL)'

--=====

get the following message

--===== Message after execution

Command(s) completed successfully.

--=====

Post #851270

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

Just the pivot code, using whatever values for the columns will work - and may be passed in later. Forget about EXECUTE for now. Just a plain old SELECT with a PIVOT in it. Have you got it to work? If so, post the code.

^{“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 13

Yeha, like this one

--=====

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 ([130], [1652], [489])

) pivotable

--=====

it returns this data grid:

--=====

codigo_item itemid_stock1301652489

141400WINNY ULT GOLD ET 12NULL511.101026.60

142151WINNY ULT GOLD ET 21NULLNULLNULL

143239WINNY ULT GOLD ET 02475.00407.80875.80

50005925BABY SEC ET 41NULLNULLNULL

--=====

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

codigo_item item id_stock 130 1652489

141400 WINNY ULT GOLD ET 12NULL 511.101026.60

142151 WINNY ULT GOLD ET 21NULL NULLNULL

143239 WINNY ULT GOLD ET 02475.00 407.80875.80

50005925 BABY SEC ET 4 1NULL NULLNULL