cross query - SQL Server Forums

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

General SQL Server Forums

New to SQL Server Programming

cross query

Author

Topic

slimt_slimt
Aged Yak Warrior

746 Posts

Posted - 2007-10-11 : 08:56:24

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2007-10-11 : 09:03:23

You have to show your actual table structure and some sample data; I am not sure what name (john,mike,wendy) means -- are those column names or values in your table? be more specific and you'll get faster and better help.

- Jeff
http://weblogs.sqlteam.com/JeffS

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-10-11 : 09:05:04

[code]DECLARE @Sample TABLE (Name VARCHAR(20), Money INT)

INSERT @Sample
SELECT 'John', 50 UNION ALL
SELECT 'John', 150 UNION ALL
SELECT 'John', 250 UNION ALL
SELECT 'Mike', 50 UNION ALL
SELECT 'Wendy', 111 UNION ALL
SELECT 'Wendy', 150

SELECT SUBSTRING(Section, 2, 20) AS Section,
SUM(CASE WHEN Name = 'John' THEN 1 ELSE 0 END) AS John,
SUM(CASE WHEN Name = 'Mike' THEN 1 ELSE 0 END) AS Mike,
SUM(CASE WHEN Name = 'Wendy' THEN 1 ELSE 0 END) AS Wendy
FROM (
SELECT CASE
WHEN Money < 100 THEN 'a< 100'
WHEN Money >= 200 THEN 'c>= 200'
ELSE 'b100-200'
END AS Section,
Name
FROM @Sample
) AS d
GROUP BY Section
ORDER BY Section[/code]

E 12°55'05.25"
N 56°04'39.16"

slimt_slimt
Aged Yak Warrior

746 Posts

Posted - 2007-10-12 : 04:48:13

i've solved it like this:

select
value,
C1,
C2,
C3

from
(select value,
SUM(CASE WHEN money between '0' and '50' then 1 else 0 END) as C1,
SUM(CASE WHEN money between '51' and '100' then 1 else 0 END) as C2,
SUM(CASE WHEN money between '101' and '200' then 1 else 0 END) as C3,

from table1
group by value) as v

group by
value,
v.C1,
v.C2,
v.C3

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-10-12 : 06:05:44

Good for you!

E 12°55'05.25"
N 56°04'39.16"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources