Display two columns from different statements

Question

Display two columns from different statements

MrCapuchino

Mr or Mrs. 500

Points: 535
More actions
July 23, 2010 at 4:46 pm

#224474

Hello,
I would like to know how do you put together two columns from 2 select statemnts.
For instance, I have a query that returns a column called JANUARY with its current data, then I want to repeat that query changing the WHERE clause parameters and returning a column called FEBRUARY, and then MARCH etc etc...
I want to put together all of those columns together like:
JANUARY | FEBRUARY | MARCH | APRIL
can you do that?
thx

Viewing 9 posts - 1 through 9 (of 9 total)

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

ColdCoffee SSC-Dedicated Points: 39972 More actions · Answer 1

I am bit unclear on the question. buddy.... can u please show some visual samples of your data??

A shot in the dark, u may use CROSS JOIN to displace results from both the qeries if both the queries will return unmatching columns...

MrCapuchino Mr or Mrs. 500 Points: 535 More actions · Answer 2

Hi

SELECT proveedor.nombre, ((SUM(MaterialColocado.utilidadneta) / objetivo.utilidad)*100) AS enero

FROM usuario INNER JOIN

objetivo ON usuario.USid = objetivo.usuario_USid INNER JOIN

proveedor ON objetivo.proveedor_Pid = proveedor.Pid INNER JOIN

MaterialColocado ON usuario.USid = MaterialColocado.usuario_USid AND proveedor.Pid = MaterialColocado.proveedor_Pid

WHERE usuario = 'George' AND (fechaoc BETWEEN '2010-01-01' AND '2010-01-31') AND WHERE usuario = 'MAURICIO LOPEZ') AS MAU

GROUP BY proveedor.nombre, objetivo.utilidad

That query results in

CATEGORY | JANUARY

CAT1 10000

CAT2 2000

CAT3 4000

I want to run that query again like this

SELECT proveedor.nombre, ((SUM(MaterialColocado.utilidadneta) / objetivo.utilidad)*100) AS febrero

FROM usuario INNER JOIN

objetivo ON usuario.USid = objetivo.usuario_USid INNER JOIN

proveedor ON objetivo.proveedor_Pid = proveedor.Pid INNER JOIN

MaterialColocado ON usuario.USid = MaterialColocado.usuario_USid AND proveedor.Pid = MaterialColocado.proveedor_Pid

WHERE usuario = 'George' AND (fechaoc BETWEEN '2010-02-01' AND '2010-02-28') AND WHERE usuario = 'MAURICIO LOPEZ') AS MAU

GROUP BY proveedor.nombre, objetivo.utilidad

This one returns

FEBRUARY

1000

2000

3000

I want to add this column and the same for the ramaining months like this

CATEGORY | JANUARY | FEBRUARY | (next months...)

CAT 1 10000 1000

and the other values, there is the same number of categories and data from month for all queries.

If you could give me another idea of how to do it I would appreciate it, I just run out of ideas :S

Thanks

ColdCoffee SSC-Dedicated Points: 39972 More actions · Answer 3

Put both the queries u have in a CROSS JOIN... match using proveedor.nombre.. this will give u what u want.. if u cant yet figure out how, drop a reply here, we will give u the query..

J Livingston SQL SSC Guru Points: 51550 More actions · Answer 4

Hi

Not sure sure if this is what are you are trying to acheive...but this may give you a few ideas

For future reference you will find you will get better and faster responses if you can present the necessary code to create and insert sample data ...this makes it far easier for people to work with.

Whilst the following does not match your names and data it hopefully provides you with an alternative approach

I have included a table "simple_calendar"...this is not necessary but you might find in future that this may well help when dealing with date groupings such as fiscal years/working days/fiscal months etc.

USE [tempdb]

GO

--- for more info http://www.sqlservercentral.com/articles/T-SQL/63681/

--- and http://www.sqlservercentral.com/articles/Crosstab/65048/

--- both articles by Jeff Moden

--- If table(s) already exists, drop

IF OBJECT_ID('tempdb..simple_calendar', 'U') IS NOT NULL

DROP TABLE simple_calendar

IF OBJECT_ID('tempdb..SOMETABLE', 'U') IS NOT NULL

DROP TABLE SOMETABLE

--- create some tables

CREATE TABLE [simple_calendar](

[date] [datetime] NOT NULL,

[MonthYear] [nvarchar](15) NOT NULL,

CONSTRAINT [PK_simple_calendar] PRIMARY KEY CLUSTERED

(

[date] ASC

)

CREATE TABLE [SOMETABLE](

[Username] [nvarchar](10) NOT NULL,

[date] [datetime] NOT NULL,

[somedata] [int] NOT NULL,

[category] [nvarchar](5) NOT NULL,

[ID] [int] IDENTITY(1,1) NOT NULL,

CONSTRAINT [PK_SOMETABLE] PRIMARY KEY CLUSTERED

(

[ID] ASC

)

--- insert some data

SET IDENTITY_INSERT [dbo].[SOMETABLE] ON;

INSERT INTO [dbo].[SOMETABLE]([Username], [date], [somedata], [category], [ID])

SELECT N'GEORGE', '20100102 00:00:00.000', 10, N'CAT1', 1 UNION ALL

SELECT N'GEORGE', '20100113 00:00:00.000', 15, N'CAT2', 2 UNION ALL

SELECT N'GEORGE', '20100215 00:00:00.000', 10, N'CAT3', 3 UNION ALL

SELECT N'GEORGE', '20100313 00:00:00.000', 5, N'CAT1', 4 UNION ALL

SELECT N'GEORGE', '20100115 00:00:00.000', 4, N'CAT1', 5 UNION ALL

SELECT N'GEORGE', '20100117 00:00:00.000', 5, N'CAT3', 6 UNION ALL

SELECT N'GEORGE', '20100117 00:00:00.000', 20, N'CAT2', 7 UNION ALL

SELECT N'GEORGE', '20100215 00:00:00.000', 3, N'CAT2', 8 UNION ALL

SELECT N'GEORGE', '20100216 00:00:00.000', 5, N'CAT3', 9 UNION ALL

SELECT N'GEORGE', '20100301 00:00:00.000', 5, N'CAT2', 10 UNION ALL

SELECT N'BILL', '20100220 00:00:00.000', 13, N'CAT1', 11 UNION ALL

SELECT N'BILL', '20100318 00:00:00.000', 3, N'CAT1', 12 UNION ALL

SELECT N'BILL', '20100122 00:00:00.000', 3, N'CAT4', 13 UNION ALL

SELECT N'BILL', '20100122 00:00:00.000', 23, N'CAT1', 14 UNION ALL

SELECT N'BILL', '20100320 00:00:00.000', 1, N'CAT2', 15 UNION ALL

SELECT N'BILL', '20100121 00:00:00.000', 8, N'CAT3', 16 UNION ALL

SELECT N'BILL', '20100206 00:00:00.000', 3, N'CAT2', 17 UNION ALL

SELECT N'FRED', '20100115 00:00:00.000', 12, N'CAT4', 18 UNION ALL

SELECT N'FRED', '20100115 00:00:00.000', 5, N'CAT1', 19 UNION ALL

SELECT N'FRED', '20100323 00:00:00.000', 1, N'CAT3', 21 UNION ALL

SELECT N'FRED', '20100323 00:00:00.000', 8, N'CAT2', 22 UNION ALL

SELECT N'FRED', '20100327 00:00:00.000', 26, N'CAT3', 24 UNION ALL

SELECT N'FRED', '20100311 00:00:00.000', 1, N'CAT2', 25

SET IDENTITY_INSERT [dbo].[SOMETABLE] OFF;

INSERT INTO [dbo].[simple_calendar]([date], [MonthYear])

SELECT '20100101 00:00:00.000', N'Jan_10' UNION ALL

SELECT '20100102 00:00:00.000', N'Jan_10' UNION ALL

SELECT '20100103 00:00:00.000', N'Jan_10' UNION ALL

SELECT '20100104 00:00:00.000', N'Jan_10' UNION ALL

SELECT '20100105 00:00:00.000', N'Jan_10' UNION ALL

SELECT '20100106 00:00:00.000', N'Jan_10' UNION ALL

SELECT '20100107 00:00:00.000', N'Jan_10' UNION ALL

SELECT '20100108 00:00:00.000', N'Jan_10' UNION ALL

SELECT '20100109 00:00:00.000', N'Jan_10' UNION ALL

SELECT '20100110 00:00:00.000', N'Jan_10' UNION ALL

SELECT '20100111 00:00:00.000', N'Jan_10' UNION ALL

SELECT '20100112 00:00:00.000', N'Jan_10' UNION ALL

SELECT '20100113 00:00:00.000', N'Jan_10' UNION ALL

SELECT '20100114 00:00:00.000', N'Jan_10' UNION ALL

SELECT '20100115 00:00:00.000', N'Jan_10' UNION ALL

SELECT '20100116 00:00:00.000', N'Jan_10' UNION ALL

SELECT '20100117 00:00:00.000', N'Jan_10' UNION ALL

SELECT '20100118 00:00:00.000', N'Jan_10' UNION ALL

SELECT '20100119 00:00:00.000', N'Jan_10' UNION ALL

SELECT '20100120 00:00:00.000', N'Jan_10' UNION ALL

SELECT '20100121 00:00:00.000', N'Jan_10' UNION ALL

SELECT '20100122 00:00:00.000', N'Jan_10' UNION ALL

SELECT '20100123 00:00:00.000', N'Jan_10' UNION ALL

SELECT '20100124 00:00:00.000', N'Jan_10' UNION ALL

SELECT '20100125 00:00:00.000', N'Jan_10' UNION ALL

SELECT '20100126 00:00:00.000', N'Jan_10' UNION ALL

SELECT '20100127 00:00:00.000', N'Jan_10' UNION ALL

SELECT '20100128 00:00:00.000', N'Jan_10' UNION ALL

SELECT '20100129 00:00:00.000', N'Jan_10' UNION ALL

SELECT '20100130 00:00:00.000', N'Jan_10' UNION ALL

SELECT '20100131 00:00:00.000', N'Jan_10' UNION ALL

SELECT '20100201 00:00:00.000', N'Feb_10' UNION ALL

SELECT '20100202 00:00:00.000', N'Feb_10' UNION ALL

SELECT '20100203 00:00:00.000', N'Feb_10' UNION ALL

SELECT '20100204 00:00:00.000', N'Feb_10' UNION ALL

SELECT '20100205 00:00:00.000', N'Feb_10' UNION ALL

SELECT '20100206 00:00:00.000', N'Feb_10' UNION ALL

SELECT '20100207 00:00:00.000', N'Feb_10' UNION ALL

SELECT '20100208 00:00:00.000', N'Feb_10' UNION ALL

SELECT '20100209 00:00:00.000', N'Feb_10' UNION ALL

SELECT '20100210 00:00:00.000', N'Feb_10' UNION ALL

SELECT '20100211 00:00:00.000', N'Feb_10' UNION ALL

SELECT '20100212 00:00:00.000', N'Feb_10' UNION ALL

SELECT '20100213 00:00:00.000', N'Feb_10' UNION ALL

SELECT '20100214 00:00:00.000', N'Feb_10' UNION ALL

SELECT '20100215 00:00:00.000', N'Feb_10' UNION ALL

SELECT '20100216 00:00:00.000', N'Feb_10' UNION ALL

SELECT '20100217 00:00:00.000', N'Feb_10' UNION ALL

SELECT '20100218 00:00:00.000', N'Feb_10' UNION ALL

SELECT '20100219 00:00:00.000', N'Feb_10' UNION ALL

SELECT '20100220 00:00:00.000', N'Feb_10' UNION ALL

SELECT '20100221 00:00:00.000', N'Feb_10' UNION ALL

SELECT '20100222 00:00:00.000', N'Feb_10' UNION ALL

SELECT '20100223 00:00:00.000', N'Feb_10' UNION ALL

SELECT '20100224 00:00:00.000', N'Feb_10' UNION ALL

SELECT '20100225 00:00:00.000', N'Feb_10' UNION ALL

SELECT '20100226 00:00:00.000', N'Feb_10' UNION ALL

SELECT '20100227 00:00:00.000', N'Feb_10' UNION ALL

SELECT '20100228 00:00:00.000', N'Feb_10' UNION ALL

SELECT '20100301 00:00:00.000', N'Mar_10' UNION ALL

SELECT '20100302 00:00:00.000', N'Mar_10' UNION ALL

SELECT '20100303 00:00:00.000', N'Mar_10' UNION ALL

SELECT '20100304 00:00:00.000', N'Mar_10' UNION ALL

SELECT '20100305 00:00:00.000', N'Mar_10' UNION ALL

SELECT '20100306 00:00:00.000', N'Mar_10' UNION ALL

SELECT '20100307 00:00:00.000', N'Mar_10' UNION ALL

SELECT '20100308 00:00:00.000', N'Mar_10' UNION ALL

SELECT '20100309 00:00:00.000', N'Mar_10' UNION ALL

SELECT '20100310 00:00:00.000', N'Mar_10' UNION ALL

SELECT '20100311 00:00:00.000', N'Mar_10' UNION ALL

SELECT '20100312 00:00:00.000', N'Mar_10' UNION ALL

SELECT '20100313 00:00:00.000', N'Mar_10' UNION ALL

SELECT '20100314 00:00:00.000', N'Mar_10' UNION ALL

SELECT '20100315 00:00:00.000', N'Mar_10' UNION ALL

SELECT '20100316 00:00:00.000', N'Mar_10' UNION ALL

SELECT '20100317 00:00:00.000', N'Mar_10' UNION ALL

SELECT '20100318 00:00:00.000', N'Mar_10' UNION ALL

SELECT '20100319 00:00:00.000', N'Mar_10' UNION ALL

SELECT '20100320 00:00:00.000', N'Mar_10' UNION ALL

SELECT '20100321 00:00:00.000', N'Mar_10' UNION ALL

SELECT '20100322 00:00:00.000', N'Mar_10' UNION ALL

SELECT '20100323 00:00:00.000', N'Mar_10' UNION ALL

SELECT '20100324 00:00:00.000', N'Mar_10' UNION ALL

SELECT '20100325 00:00:00.000', N'Mar_10' UNION ALL

SELECT '20100326 00:00:00.000', N'Mar_10' UNION ALL

SELECT '20100327 00:00:00.000', N'Mar_10' UNION ALL

SELECT '20100328 00:00:00.000', N'Mar_10' UNION ALL

SELECT '20100329 00:00:00.000', N'Mar_10' UNION ALL

SELECT '20100330 00:00:00.000', N'Mar_10' UNION ALL

SELECT '20100331 00:00:00.000', N'Mar_10'

---- following will provide a "pivot" type view grouped by Category

SELECT dbo.SOMETABLE.category,

SUM(CASE WHEN Monthyear = 'Jan_10' THEN somedata ELSE 0 END) AS JAN_10,

SUM(CASE WHEN Monthyear = 'Feb_10' THEN somedata ELSE 0 END) AS FEB_10,

SUM(CASE WHEN Monthyear = 'Mar_10' THEN somedata ELSE 0 END) AS MAR_10,

SUM(CASE WHEN Monthyear = 'Apr_10' THEN somedata ELSE 0 END) AS APR_10

FROM dbo.SOMETABLE

INNER JOIN dbo.simple_calendar ON dbo.SOMETABLE.date = dbo.simple_calendar.date

GROUP BY dbo.SOMETABLE.category

---- following will provide a "pivot" type view grouped by Category AND User

SELECT dbo.SOMETABLE.category, dbo.SOMETABLE.Username,

SUM(CASE WHEN Monthyear = 'Jan_10' THEN somedata ELSE 0 END) AS JAN_10,

SUM(CASE WHEN Monthyear = 'Feb_10' THEN somedata ELSE 0 END) AS FEB_10,

SUM(CASE WHEN Monthyear = 'Mar_10' THEN somedata ELSE 0 END) AS MAR_10,

SUM(CASE WHEN Monthyear = 'Apr_10' THEN somedata ELSE 0 END) AS APR_10

FROM dbo.SOMETABLE

INNER JOIN dbo.simple_calendar ON dbo.SOMETABLE.date = dbo.simple_calendar.date

GROUP BY dbo.SOMETABLE.Username, dbo.SOMETABLE.category

This will give you a set of results like this:

CATJAN_10FEB_10MAR_10APR_10

CAT1421380

CAT2356150

CAT31315270

CAT415000

Hopefully this is of some use.

Regards Graham

PS...if you have large data volumes or require a more flexible result set then please read Jeff Moden's articles on this site...links below....in particluar "pre agregation" and "converting to dynamic SQL"

http://www.sqlservercentral.com/articles/T-SQL/63681/

http://www.sqlservercentral.com/articles/Crosstab/65048/

________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day

MrCapuchino Mr or Mrs. 500 Points: 535 More actions · Answer 5

MrCapuchino

Mr or Mrs. 500

Points: 535

July 24, 2010 at 1:07 pm

#1197885

Thanks Everyone

J Livingston SQL SSC Guru Points: 51550 More actions · Answer 6

MrCapuchino (7/24/2010)
Thanks Everyone

You are welcome....out of cuiriosity what did you decide to use?

________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day

MrCapuchino Mr or Mrs. 500 Points: 535 More actions · Answer 7

I haven't applied anything yet, but I understand what things I could do, I will experiment with both replies in my post, and also I was told that I could create several views and then join them together, so I will see what is that about and then check what way applies better for my asp.net webpage.

Thanks again.

Phil Factor SSC-Insane Points: 20254 More actions · Answer 8

All you need to do is to extract the entire year's data, and then group by the month name. as well as the proveedor.nombre and the objetivo.utilidad and you should, as we say in East Anglia, be laughing. Excuse my code, I couldn't try it out!

SELECT Datename(month,fechaoc), proveedor.nombre, ((SUM(MaterialColocado.utilidadneta) / objetivo.utilidad)*100) AS enero

FROM usuario INNER JOIN

objetivo ON usuario.USid = objetivo.usuario_USid INNER JOIN

proveedor ON objetivo.proveedor_Pid = proveedor.Pid INNER JOIN

MaterialColocado ON usuario.USid = MaterialColocado.usuario_USid AND proveedor.Pid = MaterialColocado.proveedor_Pid

WHERE usuario = 'George' AND usuario = 'MAURICIO LOPEZ') AND (fechaoc BETWEEN '2010-01-01' AND '2010-12-31') AS MAU

GROUP BY proveedor.nombre, objetivo.utilidad, Datename(month,fechaoc)

Best wishes,
Phil Factor