Show different SELECT results in different columns

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 2005 Forums

Analysis Server and Reporting Services (2005)

Show different SELECT results in different columns

Author

Topic

Alisha
Starting Member

3 Posts

Posted - 2008-09-04 : 10:21:46

Hi,

I've just started to work with Reporting Services, and I'm trying to design a report that shows the result of two different SELECT statements in different columns , on the same row. For example , I want to show a list of items and the stock in two different locations:

Item Stock A Stock B
Item1 100 200
Item2 50 10

The information will be all in the same table, where one field will tell which location that quantity is on, so I need two different selects to get the two quantities.

Is it possible to do this? Would i need two different datasets or I can use only one?

Thanks!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-09-04 : 10:26:14

you can still get them using single select. can you give sample data from your table? then i will show you how to retrive it using single select (just 5 rows will do)

Alisha
Starting Member

3 Posts

Posted - 2008-09-04 : 10:35:02

quote:
Originally posted by visakh16

you can still get them using single select. can you give sample data from your table? then i will show you how to retrive it using single select (just 5 rows will do)

Ok, the data would be something like this:

Item Quantity Location
A 10 BLUE
A 20 RED
A 5 BLUE
B 10 BLUE
B 30 RED
B 50 BLUE
B 20 RED

And I want to show this:

Item Loc. BLUE Qty. Loc. RED Qty.
A 15 20
B 60 50

Thank you very much for your help :)

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-09-04 : 11:03:11

just use this:-

SELECT Item,
SUM(CASE WHEN Location='BLUE' THEN Quantity ELSE 0 END) AS BLUEQty,
SUM(CASE WHEN Location='RED' THEN Quantity ELSE 0 END) AS REDQty
FROM YourTable
GROUP BY Item

Alisha
Starting Member

3 Posts

Posted - 2008-09-04 : 11:15:19

That's perfect! Thanks a lot.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-09-04 : 11:34:06

Or, using SQL Server 2005 new PIVOT feature

DECLARE	@Sample TABLE
	(
		Item CHAR(1),
		Quantity TINYINT,
		Location VARCHAR(4)
	)

INSERT	@Sample
SELECT	'A', 10, 'BLUE' UNION ALL
SELECT	'A', 20, 'RED'  UNION ALL
SELECT	'A',  5, 'BLUE' UNION ALL
SELECT	'B', 10, 'BLUE' UNION ALL
SELECT	'B', 30, 'RED'  UNION ALL
SELECT	'B', 50, 'BLUE' UNION ALL
SELECT	'B', 20, 'RED'

-- Peso
SELECT		p.Item,
		p.[Red],
		p.[Blue]
FROM		@Sample AS s
PIVOT		(
			SUM(s.Quantity)
			FOR s.Location IN (Red, Blue)
		) AS p
ORDER BY	p.Item

-- Visakh16
SELECT		Item,
		SUM(CASE WHEN Location = 'Blue' THEN Quantity ELSE 0 END) AS Blue,
		SUM(CASE WHEN Location = 'Red' THEN Quantity ELSE 0 END) AS Red
FROM		@Sample
GROUP BY	Item
ORDER BY	Item

E 12°55'05.63"
N 56°04'39.26"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources