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

Viewing 7 posts - 46 through 51 (of 51 total)

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

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

Not a problem, Sam... that's what we're here for.

The graphic for SQL Server shows two result sets... one for 542 rows and one for one row. Unless you click on one of the result sets, the number in the lower right corner shows the total for both result sets. If you discount the result set of a single row return, then it will show that the query I wrote for you returns 542 rows... same as the Access query.

You're good to go.

--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)

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

I was suspecting something like that. Thank you for confirming it. Perfect. Thank you so much Jeff for all your work and contribution. I will use your code to convert the other tab queries now that I have a clue how. I feel incredibly liberated 😀

Thank you all as well for your help.

Rebooot

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

Ok, Sam... I'm tickled I could help. With that thought in mind, I have to ask, do you understand what my code did and how it works compared to the Access code? I know you'll think it strange but I'm very interested in you learning because you have the interest and that's actually kind of rare. Do you feel that you understand the code and could you pull of something similar in a slightly different circumstance? If not, you let me know and I'll explain every character of the code.

--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)

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

Well, that's is interesting to me as well, because I love learning. I really do.

Let me go over the code with you here.

ALTER PROCEDURE [dbo].[qryItemRank]

--===== Declare the I/O Parameters

@SQLPeriodIndex NVARCHAR(255),

@theMarket NVARCHAR(255),

@theSegment NVARCHAR(255)

AS

--===== Declare local variables

DECLARE @ChooseColumn VARCHAR(5),

@SqlSelect VARCHAR(MAX),

@SqlSums VARCHAR(MAX),

@SqlFrom VARCHAR(MAX)

--===== Check the value for @SQLPeriodIndex. Exit early if incorrect.

-- Could do something similar with the other input paramaters but

-- that will cost you an additional steak for each one.

IF @SQLPeriodIndex NOT BETWEEN 1 AND 6

BEGIN

RAISERROR ('Value for @SQLPeriodIndex not between 1 and 6.', 11, 1)

RETURN ---1

END

*********************************************************

** SO FAR I UNDERSTAND THE ABOVE. Now here you created a long string and based on the

** value of the @SQLPeriodIndex variable you extract from that long string the appropriate value

** that corresponds to the column name we later. I like that trick a lot. Very nice work.

*********************************************************

--===== Assign the column based on the value of SQLPeriodIndex.

-- This is a little trick to keep from having to write a lengthy CASE statement.

-- Do notice the extra space before [L4] to make it 5 characters long like the rest.

SELECT @ChooseColumn = SUBSTRING(' [L4][L12][L24][L52][YTD][YTG]',(@SQLPeriodIndex-1)*5+1,5)

*********************************************************

** Here you are setting the variable @SqlSelect and then padding it with 10 additional characters

*********************************************************

--===== Create the SELECT part of our dynamic SQL

SELECT @SqlSelect = 'SELECT d3.Tag,' + CHAR(10)

*********************************************************

** HERE I need help

** I can see here that you are using STUFF to replace the first two characters with the empty string. Why?

** The @SqlSums is being set to a select statement that is a little confusing to me. I understand the join but I don't understand

** the field list being selected. Help is appreciated here.

*********************************************************

--===== Create the distinct "SUM"s of our dynamic SQL for each found Fact name

SELECT @SqlSums = STUFF((SELECT ',' + CHAR(10)

+ 'SUM(CASE WHEN d3.Fact = ''' + d3.Fact

+ ''' THEN ' + @ChooseColumn + ' ELSE 0 END) AS '

+ QUOTENAME(d3.Fact)

FROM dbo.Data_3 d3

INNER JOIN dbo.SKU_CONTENTS sku

ON d3.Tag = sku.TAG

WHERE d3.Market = @theMarket

AND sku.SEGMENT = @theSegment

GROUP BY d3.Fact

FOR XML PATH(''))

,1,2,'')

--===== Create the FROM part of our dynamic SQL

SELECT @SqlFrom = CHAR(10) + ' FROM dbo.Data_3 d3

INNER JOIN dbo.SKU_CONTENTS sku

ON d3.Tag = sku.TAG

WHERE d3.Market = ''' + @theMarket + '''

AND sku.SEGMENT = ''' + @theSegment + '''

GROUP BY d3.Tag'

--===== Ready, aim, fire...

EXEC (@SqlSelect + @SqlSums + @SqlFrom)

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

*********************************************************
** Here you are setting the variable @SqlSelect and then padding it with 10 additional characters
*********************************************************
--===== Create the SELECT part of our dynamic SQL
SELECT @SqlSelect = 'SELECT d3.Tag,' + CHAR(10)

Good guess but no... I'm adding the NewLine or LineFeed character just as if you had typed the code and pressed {enter}.

*********************************************************
** HERE I need help
** I can see here that you are using STUFF to replace the first two characters with the empty string. Why?
** The @SqlSums is being set to a select statement that is a little confusing to me. I understand the join but I don't understand
** the field list being selected. Help is appreciated here.
*********************************************************

The STUFF is a wrapper around XML that concatenates the "select list" together. Each item starts with a comma and a linefeed. For the first line, we don't need that so the STUFF get's rid of the first comma and linefeed.

The rest of the code concatenates SUM(CASE..... END) AS [columnname] + linefeed for each field name (column name) we're trying to build as dynamic SQL.

Change the following...

--===== Ready, aim, fire...

EXEC (@SqlSelect + @SqlSums + @SqlFrom)

... to ...

--===== Ready, aim, fire...

PRINT @SqlSelect + @SqlSums + @SqlFrom

... and see what the code ends up looking like.

--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)

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

Not only I got it now. I just created my own new query in a different scenario. Man, it is so nice when there is a light at the end of the tunnel.

Thank you so much, kind Sir.

You are written down in my good books.

Cheers,

Sam

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

Test-170228 (12/7/2009)
Not only I got it now. I just created my own new query in a different scenario. Man, it is so nice when there is a light at the end of the tunnel.

Heh... better than that, it's nice when the light at the end of the tunnel isn't a train. 😛

I appreciate the gratious offer you made previously but I can't accept it. I'm just passing it forward. I can't help everyone (heh... I'm not that bloody smart :-D) but the reason I do this is that I made a vow to myself way back in 1997 when I couldn't find the answer to an SQL question even through Microsoft. My vow was that I'd help people get help so they could learn well enough for them to pass it forward, as well.

Pass it forward, Sam. When someone runs into a jam with SQL Server, see if you can find the answer and help them well enough so they can pass it forward, too. In the long run, you'll be helping yourself more than you can possibly imagine. For example, I learned quite a lot about some things I've never had to do before by solving this problem... the dynamic cross-tab was easy... applying it to your particular data using a language (Access) that I really knew nothing about (I do NOW) as a model? Now that was fun. 😉

And thanks for the wonderful compliment... you made my day by telling me you were able to do one on your own. 🙂 Hopefully, other people that happen across this thread will be able to, as well.

--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)