Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
bugulgad
Starting Member
21 Posts |
 Posted - 2009-03-02 : 19:03:44
|
| Assuming "stacked tables" is an appropriate term for this situation.Here is an example of a table design I am running into.company, EventDate makes up the composite primary keyCOMPANY EVENTDATE valueA valueB TYPESABC 03/02/09 constant 1 Type1ABC 03/02/09 constant 2 Type2ABC 03/02/09 constant 3 Type3So basically the rows are almost identical. You only encounter different values at the last 2 columns. It seems like the design wants to keep the table purposefully narrow yet not wanting to have any relationships to other tables.How can I query this table to just return 1 row. Note it is not important for the columns to be aliased, we will assume the value on the left always refers to the Type listed on the right.So I imagine the single query result set to look like thisCOMPANY EVENTDATE valueA valueB TYPES valueB TYPES valueB TYPESABC 03/02/09 constant 1 Type1 2 Type2 3 Type3 |
|
|
Nageswar9
Aged Yak Warrior
600 Posts |
Posted - 2009-03-03 : 00:02:31
|
| select company,eventdate,valuea,max(case when valueb = 1 then valueb end) as valub,max(case when valueb = 1 then TYPES end) as TYPES, max(case when valueb = 2 then valueb end) as valub,max(case when valueb = 2 then TYPES end) as TYPES,max(case when valueb = 3 then valueb end) as valub,max(case when valueb = 3 then types end) as TYPES from @tempgroup by company,eventdate,valuea |
 |
|
|
bugulgad
Starting Member
21 Posts |
Posted - 2009-03-03 : 13:52:49
|
| Sorry I could have been clearer, the valueB values is not known, we want to be able to select those values.I was only trying to attempt to illustrate that the values in that column keep changing. The values in column TYPES is what is known. |
|
|
|
vijayisonly
Master Smack Fu Yak Hacker
1836 Posts |
Posted - 2009-03-03 : 14:49:41
|
| Then... can't you just change the CASE statement in Nagesh's query to use TYPES instead of valub? |
|
|
|
TG
Master Smack Fu Yak Hacker
6065 Posts |
Posted - 2009-03-03 : 16:03:57
|
| Are you saying that you don't know the number of columns OR the column names you will end up with because the data could be anything? if so, check out this article and read the comments link:http://www.sqlteam.com/article/dynamic-cross-tabs-pivot-tablesBe One with the OptimizerTG |
|
|
|
bugulgad
Starting Member
21 Posts |
Posted - 2009-03-03 : 18:10:02
|
quote:
Originally posted by vijayisonly
Then... can't you just change the CASE statement in Nagesh's query to use TYPES instead of valub?
Yes this has seemed to work. Thanks all. |
|
|
|
|
|
|
|
|
How to query stacked tables