grouping 2 or more records into one colum

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

Transact-SQL (2000)

grouping 2 or more records into one colum

Author

Topic

BenSwitzer
Yak Posting Veteran

72 Posts

Posted - 2007-09-27 : 13:46:04

Hello I have data that looks like this:

Grp Code PG
1 185 8
1 185 9
1 200 4
1 200 5
1 200 6

I've been trying to create a query that outputs:
Grp Code PG
1 185 89
1 200 456

I have not been able to come with anything that creates this output. Any ideas?

Thanks

Kristen
Test

22859 Posts

Posted - 2007-09-27 : 13:58:16

This perhaps?

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=55210&SearchTerms=Pivot+table,Pivot+Tables,Dynamic+CrossTabs

Kristen

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-09-27 : 14:10:48

[code]-- Prepare sample data
DECLARE @Sample TABLE (Grp INT, Code INT, PG INT)

INSERT @Sample
SELECT 1, 185, 8 UNION ALL
SELECT 1, 185, 9 UNION ALL
SELECT 1, 200, 4 UNION ALL
SELECT 1, 200, 5 UNION ALL
SELECT 1, 200, 6

-- Show the expected output
SELECT Grp,
Code,
SUM(xPG) AS PG
FROM (
SELECT s1.Grp,
s1.Code,
s1.PG * POWER(10, (SELECT COUNT(*) FROM @Sample AS s2 WHERE s2.Grp = s1.Grp AND s2.Code = s1.Code AND s2.PG > s1.PG)) AS xPG
FROM @Sample AS s1
) AS d
GROUP BY Grp,
Code
ORDER BY Grp,
Code[/code]

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

BenSwitzer
Yak Posting Veteran

72 Posts

Posted - 2007-09-27 : 15:10:07

Kristen, thanks for the reference. I will look at the solutions.

Paco, I get an arithmetic error when trying to use this for my table.
(12 row(s) affected)

Server: Msg 232, Level 16, State 3, Line 1
Arithmetic overflow error for type int, value = 10000000000.000000.

Thanks

I expect that I somehow forgot about cursor :+)

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-09-28 : 02:49:00

As usual, if you use front end application, do concatenation there
or refer http://sqljunkies.com/WebLog/amachanic/archive/2004/11/10/5065.aspx

Madhivanan

Failing to plan is Planning to fail

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-09-28 : 03:10:54

If you have posted better sample data, you would not have gotten this error.
The works for the sample data posted above.

Also you don't provide information about there can be two or more PG for any combination of Grp and Code.

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

BenSwitzer
Yak Posting Veteran

72 Posts

Posted - 2007-09-28 : 08:01:54

THanks Madhivanan That did the trick. Sorry Peso for the lack of information. Thanks for your help :+)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources