Cross Tab query

Question

Cross Tab query

Viewing 4 posts - 16 through 18 (of 18 total)

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

sqllearner-339367 Hall of Fame Points: 3004 More actions · Answer 1

HI Jeff/Luke,

During my research I discovered that combination of CTE and Row_Number function could answer my problem. Im in the process of working out that. Any suggestions is valuable.

Cheers

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

vidhyasudha (10/5/2008)
HI Jeff,
Temp table will be the structure I mentioned. Two base tables doesn't share any relation. This is due to the fact that database is migrated to sql 2005 with out any change in the datamodel. They have their business application running on ASP and now Im working on their ASP.NET migration. In addition ASP.NET display all the values when creating new group from table 1 and stores the value to the Table2. ie is to say subsequent edit/update will be stored in Table2. I have thought adding extra column in temptable will solve the problem for new/edit operation. Your suggestion is very much valued.
Cheers

If we go your route, all you'll end up with is a copy of Table1 with dupes and some bits. Don't you want to include the GROUP from Table2 in the temp table?

And, no... I don't see how a CTE that uses ROW_NUMBER will do it without a hitch. This is a simple unpivot.

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

sqllearner-339367 Hall of Fame Points: 3004 More actions · Answer 3

HI Jeff,

As you say I agree that CTE with Row_Number is not helpful. I will try with your option UnPivot and let you know the result.

Cheers

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

You may also want to try using multiple Selects separated by UNION ALL.

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