need help transforming SQL Server results set in SQL

need help transforming SQL Server results set in SQL

Post by Nayan Rava » Fri, 25 Apr 1997 04:00:00




>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



Hi Brent,

The following query will give the required results:

-- Create table of day names and their position in the final result set
create table daypos
(
daystr char(3) not null,
position tinyint not null
)
go

-- Insert the rows
insert daypos values ("mon",1)
insert daypos values ("tue",2)
insert daypos values ("wed",3)
insert daypos values ("thu",4)
insert daypos values ("fri",5)
insert daypos values ("sat",6)
insert daypos values ("sun",7)
go

-- Now the query
select  
p.daystr,
"A" = isnull(sum(y.total*(1-abs(sign(ascii("A")-ascii(y.code))))),0),
"B" = isnull(sum(y.total*(1-abs(sign(ascii("B")-ascii(y.code))))),0),
"C" = isnull(sum(y.total*(1-abs(sign(ascii("C")-ascii(y.code))))),0)
from yourtable y, daypos p
where p.daystr *= y.daystr
group by p.daystr, p.position
order by p.position
go

The outer join and isnull ensure that you get a "day 0 0 0" displayed
for days where there are no A, B or C. For example, if you delete the
"thu" row in your example data and get rid of the isnull and change the
outer join to a normal join,  you'll get no "thu" in the result set.

The technique I've used is explained in "Optimizing Transact-SQL:
Advanced Programming Techniques" by David Rozenshtein, Anatoly
Abramovich and Eugene Birger (publisher SQL Forum Press). Contains some
good examples of table transformations.

HTH
--
Nayan Raval

 
 
 

1. Need help transforming results set format

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


--

--

2. drivers for php on windows

3. HELP Needed - Heavy Load causing Sparatic Empty Result Sets (SQL 7)

4. The process could not deliver update(s) at the Subscriber.

5. IDC/HTX or ASP?//

6. Transforming Access 2002 SQL to SQL-Server 2000 SQL

7. Result Sets easy one I need help need Asap Please guys

8. SQL Transform help needed

9. need help using TRANSFORM (SQL)

10. Need help to set the default date format in SQL Server

11. Help Needed Setting Up SQL Server 7.0 Mail/Operators