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 15 posts - 31 through 45 (of 51 total)

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

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

Hey Jeff,

How goes the battle? I am trying to figure things out on my end and I feel my efforts have been in vain. I hope yours are not.

I found this article and I am trying to use it: http://www.kodyaz.com/articles/t-sql-pivot-tables-in-sql-server-tutorial-with-examples.aspx

Don't look at it if it is too distracting.

Thanks again.

Rebooot

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

TheSQLGuru (12/3/2009)
Jeff Moden (12/3/2009)
Heh... nope... I want the steaks. 😛
Thought Pork Chops were your thing?? :hehe:

Heh... that's what I feed other people. :hehe:

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

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

Test-170228 (12/3/2009)
Hey Jeff,
How goes the battle? I am trying to figure things out on my end and I feel my efforts have been in vain. I hope yours are not.
I found this article and I am trying to use it: http://www.kodyaz.com/articles/t-sql-pivot-tables-in-sql-server-tutorial-with-examples.aspx
Don't look at it if it is too distracting.
Thanks again.
Rebooot

Almost done... I can't test it because I don't have your data so I'm doing a final deskcheck to make sure I haven't screwed up the code too badly. 😉

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

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

Heh... I could build some test data but then you'd have to add a bottle of Scotch to the steak order. :w00t:

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

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

Ok... barring any major oversights or simple typo's, I think this will do it. It should be an exact replacement in that if you folks add another "Fact" to Data_3, it won't require any changes to the code.

As a side bar, I just can't understand why MS didn't make such a slick PIVOT function for SQL Server as they did for ACCESS. A Dynamic Cross-Tab in SQL Server is easier and prettier to do than a PIVOT in SQL Server.

CREATE 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

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

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

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

--===== 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 dt.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 dt.Market = ''' + @theMarket + '''

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

GROUP BY d3.Tag'

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

EXEC (@SqlSelect + @SqlSums + @SqlFrom)

GO

If it turns out to be a little slow, we can add Peso's "pre-aggregation" trick to it but, from what I saw in the data, this should be just fine.

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

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

Test-170228 (12/2/2009)
Man, you get this right, and I am going to send you a cheque for some pizza money for sure or steak 😉

Heh... PM me... I'll send you the address to send the steaks to. 😛

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

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

RandMan-585256 (12/3/2009)
Since this is Access VBA why don't you use the cross-tab query type in Access?

Hmmm.... not a bad idea. Do you believe that it would work on a 64 bit SQL Server?

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

RandMan-585256 Old Hand Points: 310 More actions · Answer 8

Jeff Moden (12/3/2009)
RandMan-585256 (12/3/2009)
Since this is Access VBA why don't you use the cross-tab query type in Access?
Hmmm.... not a bad idea. Do you believe that it would work on a 64 bit SQL Server?

I don't know why not. Connect to the SQL Server through Access and link the table(s) back to Access.

Then use the cool 'Cross-Tab' query in Access to get your results. Don't use the cross-tab wizard, but build the query in the graphical query builder choosing a query type of 'Cross-Tab'

If you are not able to connect to the SQL 64 bit for whatever reason, then I would look at exporting the data/table from SQL and have Access import it back in.

You should be able to connect Access to SQL via ODBC, OLEDB, or native SQL drivers.

"There are three kinds of men. The one that learns by reading. The few who learn by observation. The rest of them have to pee on the electric fence for themselves." -- Will Rogers

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

So! What say yea, 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)

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

RandMan-585256 (12/4/2009)
Jeff Moden (12/3/2009)
RandMan-585256 (12/3/2009)
Since this is Access VBA why don't you use the cross-tab query type in Access?
Hmmm.... not a bad idea. Do you believe that it would work on a 64 bit SQL Server?
I don't know why not. Connect to the SQL Server through Access and link the table(s) back to Access.
Then use the cool 'Cross-Tab' query in Access to get your results. Don't use the cross-tab wizard, but build the query in the graphical query builder choosing a query type of 'Cross-Tab'
If you are not able to connect to the SQL 64 bit for whatever reason, then I would look at exporting the data/table from SQL and have Access import it back in.
You should be able to connect Access to SQL via ODBC, OLEDB, or native SQL drivers.

Ummm... ok... I'm just a bit confused. I agree with what you're saying... I use pass-through queries from Access to SQL Server on a regular basis. But the goal of the current task is to return the Access Pivot as results in SQL Server without having to actually open Access manually.

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

Hey Jeff,

The code looks great. For some reason though the code return 543 rows while the access database query running on the same data set with the same parameters returns 542.

I am trying to trace the reason. The hunt is on the way. I will keep you posted.

Any ideas?

I am attaching both.

Make sure PM your address for the steaks ...

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

The JPG's won't help me or you. I suspect the devil is in the data. My recommendation is that you need to start with a row count check between the Access Tables and the SQL Server tables to make sure that nothing was dropped or added in the transfer process you must have gone through. It could also be possible that someone added a row to the SQL Server side after the transfer, No?

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

Matt Miller (4) SSC Guru Points: 124210 More actions · Answer 13

Jeff Moden (12/5/2009)
The JPG's won't help me or you. I suspect the devil is in the data. My recommendation is that you need to start with a row count check between the Access Tables and the SQL Server tables to make sure that nothing was dropped or added in the transfer process you must have gone through. It could also be possible that someone added a row to the SQL Server side after the transfer, No?

Actually - I beg to differ. The JPG's show you everything you need to know.:-P:-D

The outputs ARE the same. I suspect you might be looking a long time for the differences in the XTab.

Just run the stored proc by itself.....

----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

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

Heh... you know what, Matt? You're right. The extra 1 count came from the extra return on the one JPG. They are, in fact, identical. Thanks for the double check.

Sam... do you understand why we say they are the same?

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

I am trying but since I started this exercise I have shown little experience I will continue to be persistent in my ignorance. I do not really understand. :unsure: