Need help transforming results set format

Thread View: comp.databases.ms-sqlserver

2 messages

[T] Back to Threads [V] Tree View [G] Group Articles

2 total messages Started by Brent Eubanks Thu, 24 Apr 1997 00:00

#52

Author: Brent Eubanks
Date: Thu, 24 Apr 1997 00:00

87 lines
1978 bytes


I have a query that generates a results set of the following format:


DAY CODE TOTAL
--- ---- -----
mon   A    5
tue   A   10
wed   A    7
fri   A   20
sat   A    5
mon   B   12
thu   B    3
fri   B   16
sat   B    9
sun   B   23
tue   C    5
wed   C   21
sat   C   17


I need to put it into the following format:

Day   A   B   C
---  --- --- ---
mon   5  12   0
tue  10   0   5
wed   7   0  21
thu   0   3   0
fri  20  16   0
sat   5   9  17
sun   0  23   0

Can anyone suggest a way to do this other than inserting each value
with an individual update statement?  (There are 21 values in this
sample set.  The real thing is much to big to do peicemeal.)



ALTERNATELY:

If my starting set look like this

DAY CODE TOTAL
--- ---- -----
mon   A    5
tue   A   10
wed   A    7
thu   A    0
fri   A   20
sat   A    5
sun   A    0
mon   B   12
tue   B    0
wed   B    0
thu   B    3
fri   B   16
sat   B    9
sun   B   23
mon   C    0
tue   C    5
wed   C   21
thu   C    0
fri   C    0
sat   C   17
sun   C    0

ie: The empty values are filled with zeroes, I can create the format I
want by doing a multiple self-join.  However, to make this work, I
need an efficient way to insert all those missing zeroes.  Any ideas?


Thanks

Brent

if possible, please cc a copy of your reply to brent@ugcs.caltech.edu





--

--
Brent Eubanks					    brent@ugcs.caltech.edu

Re: Need help transforming results set format

#53

Author: Michael Bilhartz
Date: Thu, 24 Apr 1997 00:00

52 lines
1749 bytes

Brent Eubanks wrote:
> 
> I have a query that generates a results set of the following format:
> 
> DAY CODE TOTAL
> --- ---- -----
> mon   A    5
> tue   A   10
> wed   A    7
> fri   A   20
> sat   A    5
> mon   B   12
> thu   B    3
> fri   B   16
> sat   B    9
> sun   B   23
> tue   C    5
> wed   C   21
> sat   C   17
> 
> I need to put it into the following format:
> 
> Day   A   B   C
> ---  --- --- ---
> mon   5  12   0
> tue  10   0   5
> wed   7   0  21
> thu   0   3   0
> fri  20  16   0
> sat   5   9  17
> sun   0  23   0
> 
> Can anyone suggest a way to do this other than inserting each value
> with an individual update statement?  (There are 21 values in this
> sample set.  The real thing is much to big to do peicemeal.)

> . . .

I think this will work but I haven't tested it:

SELECT Day, 	MAX(CASE WHEN Code = A THEN Total ELSE 0 END) A, 
		MAX(CASE WHEN Code = B THEN Total ELSE 0 END) B, 	
		MAX(CASE WHEN Code = C THEN Total ELSE 0 END) C 
FROM Days
GROUP BY Day

Prior to the CASE statement, this was a lot harder to do, you had to figure 
out a way to do it with the ISNULL function.

Regards and good luck,
-Mike Bilhartz

Thread Navigation

This is a paginated view of messages in the thread with full content displayed inline.

Messages are displayed in chronological order, with the original post highlighted in green.

Use pagination controls to navigate through all messages in large threads.

Back to All Threads