Crosstab query - Please help. it has been a few days without sleep

Question

Crosstab query - Please help. it has been a few days without sleep

Test-170228

SSC Enthusiast

Points: 126
More actions
December 1, 2009 at 3:56 pm

#214424

I need someone to re-write this code for me for the SQL server 2008. I would greatly appreciate it:
PARAMETERS SQLPeriodIndex Text ( 255 ), theMarket Text ( 255 ), theSegment Text ( 255 );
TRANSFORM Sum(Choose([SQLPeriodIndex],[L4],[L12],[L24],[L52],[YTD],[YTG])) AS SumOf
SELECT Data_3.Tag
FROM Data_3 INNER JOIN SKU_CONTENTS ON Data_3.Tag=SKU_CONTENTS.TAG
WHERE (((Data_3.Market)=[theMarket]) AND ((SKU_CONTENTS.SEGMENT)=[theSegment]))
GROUP BY Data_3.Tag
PIVOT Data_3.Fact;
Cheers for all of your SQL Server gurus,
Rebooot

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

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

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

Test-170228 (12/1/2009)
I need someone to re-write this code for me for the SQL server 2008. I would greatly appreciate it:
PARAMETERS SQLPeriodIndex Text ( 255 ), theMarket Text ( 255 ), theSegment Text ( 255 );
TRANSFORM Sum(Choose([SQLPeriodIndex],[L4],[L12],[L24],[L52],[YTD],[YTG])) AS SumOf
SELECT Data_3.Tag
FROM Data_3 INNER JOIN SKU_CONTENTS ON Data_3.Tag=SKU_CONTENTS.TAG
WHERE (((Data_3.Market)=[theMarket]) AND ((SKU_CONTENTS.SEGMENT)=[theSegment]))
GROUP BY Data_3.Tag
PIVOT Data_3.Fact;
Cheers for all of your SQL Server gurus,
Rebooot

Since the TEXT datatype takes no operand and cannot be defined as to length in SQL Server, you should probably start with telling us what "flavor" of SQL this is written in. The next step would be for you to start to help yourself by looking up the PIVOT operator in Books Online (the "Help" system that comes with SQL Server) and familiarize yourself with it so you can actually understand what's going on with the PIVOT if someone actually does do a rewrite for you. Hell, you might even figure it out for yourself once you get started. 😉

--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)
Intro to Tally Tables and Functions

Test-170228 SSC Enthusiast Points: 126 More actions · Answer 2

Thanks. This code is written in VBA Access. I have been reading much about PIVOT code over the past four days without luck to solve this. I appreciate your humor, but I wouldn't have posted this except that I had to.

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 3

I can give it a try but a little more info would help me a lot... any chance you could post the CREATE TABLE statements for the Data_3 and SKU_CONTENTS tables?

--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)
Intro to Tally Tables and Functions

Test-170228 SSC Enthusiast Points: 126 More actions · Answer 4

Thank you very much, Jeff. Here is the create statement for Data_3:

CREATE TABLE [dbo].[Data_3](

[Market] [nvarchar](255) NOT NULL,

[Tag] [nvarchar](255) NOT NULL,

[Fact] [nvarchar](255) NOT NULL,

[P25] [real] NULL,

[P24] [real] NULL,

[P23] [real] NULL,

[P22] [real] NULL,

[P21] [real] NULL,

[P20] [real] NULL,

[P19] [real] NULL,

[P18] [real] NULL,

[P17] [real] NULL,

[P16] [real] NULL,

[P15] [real] NULL,

[P14] [real] NULL,

[P13] [real] NULL,

[P12] [real] NULL,

[P11] [real] NULL,

[P10] [real] NULL,

[P9] [real] NULL,

[P8] [real] NULL,

[P7] [real] NULL,

[P6] [real] NULL,

[P5] [real] NULL,

[P4] [real] NULL,

[P3] [real] NULL,

[P2] [real] NULL,

[P1] [real] NULL,

[P0] [real] NULL,

[L52] [real] NULL,

[L24] [real] NULL,

[L12] [real] NULL,

[L4] [real] NULL,

[YTD] [real] NULL,

[L52_LY] [real] NULL,

[L24_LY] [real] NULL,

[L12_LY] [real] NULL,

[L4_LY] [real] NULL,

[YTD_LY] [real] NULL,

[YTG] [real] NULL,

[YTG_LY] [real] NULL,

[SSMA_TimeStamp] [timestamp] NOT NULL,

And for the SKU_Content:

CREATE TABLE [dbo].[SKU_CONTENTS](

[TAG] [nvarchar](25) NOT NULL,

[SEGMENT] [nvarchar](255) NOT NULL,

CONSTRAINT [SKU_CONTENTS$PrimaryKey] PRIMARY KEY CLUSTERED

(

[TAG] ASC,

[SEGMENT] ASC

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

) ON [PRIMARY]

Regards,

Sam

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 5

If knowledge about ACCESS were gasoline, I wouldn't have enough to run a sugar-ant's mini-bike through a match box. With that in mind, thanks for posting the tables... I understand much more of what is going on there. I just need one more piece of info... do you have an output from the ACCESS version of the query that you could attach to your next post? I don't know for sure but maybe even just a screen shot would do.

Thanks, Sam.

--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)
Intro to Tally Tables and Functions

Test-170228 SSC Enthusiast Points: 126 More actions · Answer 6

Hi Jeff,

Here is a pdf of the the first few lines.

I really appreciate your help.

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 7

Must be a different query. The output you've shown returns 19 columns... the query you've shown returns only 7. Not to worry, though... I think I have it and I'll be back in the morning.

--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)
Intro to Tally Tables and Functions

Test-170228 SSC Enthusiast Points: 126 More actions · Answer 8

The screen capture was right after I ran that query with no modification. Isn't that odd? But anyhow, the output is what I care about to look like the screen capture. How Access does it maybe a distraction at this point. Who knows how jet works sometimes.

I will wait for your feedback. Thanks again for your help.

Sam

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 9

I believe I may have it figured... I just need some confirmation... any chance of you posting the CREATE TABLE (or whatever) from ACCESS for the Date_3 table (I think it's different in Access than what you have in SQL Server). I think the table in Access is actually an EAV or NVP fact table with 3 different values for Data_3.Fact and that's where the 19 columns came from... 7-1 from the query (period removed because not pivoted) leaving 6 columns * 3 facts (1 for each apparent section of the report) for a total of 18 columns. Add the period back in and, Viola! ;-), 19 columns.

--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)
Intro to Tally Tables and Functions

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 10

Hmmmm... thinking about it a bit more, the tables may actually be identical for column names. If that's true, I don't need the CREATE statement... just confirmation that's true.

--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)
Intro to Tally Tables and Functions

Test-170228 SSC Enthusiast Points: 126 More actions · Answer 11

Hi Jeff,

I am not in the office right now so I can't check to confirm. However, the Data_3 file in SQL Server was the up-sized table from the ACCESS database. I would presume the structure is exactly the same.

I would love to understand your calculation better, but I will wait for your code to see if I can understand it better.

Test-170228 SSC Enthusiast Points: 126 More actions · Answer 12

Hey Jeff,

I am confirming that both tables are identical.

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 13

Perfect... thanks for your patience. It sometimes takes me a while to sort things out especially with our combined lack of knowledge. 😉

--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)
Intro to Tally Tables and Functions

Test-170228 SSC Enthusiast Points: 126 More actions · Answer 14

Man, you get this right, and I am going to send you a cheque for some pizza money for sure or steak 😉