Multiple query results in one row

Question

Post reply

Multiple query results in one row

Ghanta Bro

Ten Centuries

Points: 1079
More actions
October 26, 2005 at 11:53 am

#88335

I need help please...

Say I have three Group Types like:
A
B
C
I have to write one select statement that will be give me number of A's, B's , C's and total in table .. the result should be
A B C Total
3 5 6 14

Can anyone help me in this please. Thanks in advance.

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Jo Pattyn SSC-Dedicated Points: 32348 More actions · Answer 1

something from the books online (pivot table)

SELECT togroupby,

SUM(CASE Group WHEN 1 THEN Amount ELSE 0 END) AS A,

SUM(CASE Group WHEN 2 THEN Amount ELSE 0 END) AS B,

SUM(CASE Group WHEN 3 THEN Amount ELSE 0 END) AS C,

count(*) as TOTAL

FROM ...

GROUP BY togroupby

Farrell Keough SSCoach Points: 17414 More actions · Answer 2

If you do not know all your Groups and want to do this dynamically, this is clumsy, but it will work...

CREATE TABLE #Counting( [Name] varchar(1),

[Count] integer)

INSERT INTO #Counting

SELECT 'A', 2 UNION ALL

SELECT 'A', 1 UNION ALL

SELECT 'B', 2 UNION ALL

SELECT 'B', 1 UNION ALL

SELECT 'C', 3 UNION ALL

SELECT 'C', 3

CREATE TABLE #OutPut( [Total] integer)

INSERT INTO #OutPut( [Total])

SELECT SUM( [Count]) FROM #Counting

DECLARE @CurrentName varchar(1),

@MaxName varchar(1),

@sql varchar(2000)

SET @CurrentName = (SELECT MIN( [Name]) FROM #Counting)

SET @MaxName = (SELECT MAX( [Name]) FROM #Counting)

WHILE @CurrentName <= @MaxName

BEGIN

SET @sql = 'ALTER TABLE #OutPut ADD [' + @CurrentName + '] integer NULL '

EXEC( @sql)

SET @sql = ''

SET @sql = 'UPDATE #OutPut SET [' + @CurrentName + '] = ' +

'(SELECT SUM( [Count]) FROM #Counting WHERE [Name] = ' +

CHAR(39) + @CurrentName + CHAR(39) + ')'

EXEC( @sql)

SET @CurrentName = (SELECT MIN( [Name]) FROM #Counting WHERE [Name] > @CurrentName)

END

SELECT * FROM #OutPut

DROP TABLE #OutPut

DROP TABLE #Counting

I wasn't born stupid - I had to study.

RyanRandall SSChampion Points: 13623 More actions · Answer 3

Here's another way to do it if don't know the groups (using Farrell's #Counting table). This code below gives the following result, and is safe to run.

A B C TOTAL

----------- ----------- ----------- -----------

3 5 6 14

CREATE TABLE #Counting( [Name] varchar(1), [Count] integer)

INSERT INTO #Counting

SELECT 'A', 2 UNION ALL

SELECT 'A', 1 UNION ALL

SELECT 'B', 2 UNION ALL

SELECT 'B', 1 UNION ALL

SELECT 'C', 3 UNION ALL

SELECT 'C', 3

DECLARE @s-2 VARCHAR(8000)

SET @s-2 = ''

SELECT @s-2 = @s-2 + 'SUM(CASE Name WHEN ''' + Name + ''' THEN Count ELSE 0 END) AS ' + Name + ',' + char(13)

FROM (SELECT DISTINCT Name FROM #Counting) a

EXEC ('SELECT ' + @s-2 + ' SUM(Count) as TOTAL FROM #Counting')

DROP TABLE #Counting

Regards,

Ryan

Ryan Randall

Solutions are easy. Understanding the problem, now, that's the hard part.

Farrell Keough SSCoach Points: 17414 More actions · Answer 4

Very nice! Thanks Ryan - I will save this...

I wasn't born stupid - I had to study.