Select same column twice, display results in one row

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.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Select same column twice, display results in one row

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2004-02-10 : 07:59:18

René writes "I'd like to make a query where i can display the same column twice, but have the results in one row, seperated by a value in the table. Probably not very clear, but here's an example:
The table has these fields:
week (int), sales(int), reoccurring(bit).

I'd like to display the results as:
week, reoccurringsales, onetimesales
one line per period.

To be able to select an extra column, i added a dummyfield to the table.

So far i figured out this:

SELECT week, sales AS onetimesales, dummyfield AS reoccurringsales
FROM sales
WHERE reoccurring = 0
UNION
SELECT week, dummyfield AS reocurringsales, sales AS onetimesales
FROM sales
WHERE reoccurring = 1

But this would display 2 lines per period (if it has both type of sales of course).
Is there a way to 'merge' the two fields per period?
I cannot change tablelayout or data because another application uses this table as well.

Thanks in advance."

gpl
Posting Yak Master

195 Posts

Posted - 2004-02-10 : 08:07:35

Rene
This is the time to use CASE, I think this might be the answer

SELECT
week,
CASE reoccurring
When 0 Then sales
Else -1
END AS onetimesales,
CASE reoccurring
When 1 Then sales
Else -1
END AS reoccurringsales
FROM sales

Graham

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2004-02-10 : 08:56:32

close ... to consolidate multiple rows into 1 row, by definition you need to perform some sort of summarization or aggregation of the values in the multiple rows. In this case, it appears SUM() is the way to go. And you then need to decide what your desired grouping is; i.e., "return 1 row per *something*". in this case, it appears to be Week.


SELECT Week, SUM(CASE WHEN reocurring=1 then Sales else 0 END) as ReOccurringSales,
   SUM(CASE WHEN ReOccurring=0 then Sales else 0 end) as OneTimeSales
FROM
Sales
GROUP BY Week

- Jeff

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources