Additional rows in result - 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

Additional rows in result

Author

Topic

mkdlmr
Starting Member

21 Posts

Posted - 2014-02-12 : 16:42:25

Hi All,

I have two tables that I am pulling data from to insert into a third table.

Table 1:
Organization Address Date
Name 404 St. 12/31/1999

Table 2:
Organization Software Type Quantity
Name SW1 5
Name SW2 6
Name SW3 7
Name SW4 8

My query looks something like this:

INSERT INTO Organization SW Reqs (Name, Address, Date, SW1 Quantity, SW2 Quantity, SW3 Quantity)
SELECT Table 1.Name, Table 1.Address, Table 1.Date,
(Select Table 2.Quantity Where Table 2.Software Type = 'SW1'),
(Select Table 2.Quantity Where Table 2.Software Type = 'SW2'),
(Select Table 2.Quantity Where Table 2.Software Type = 'SW3')
FROM Table 1, Table 2
WHERE Table 1.Organization = Table 2. Organization

Sadly, this query gives me four rows, one for each Software Type. I have tried putting DISTINCT after my SELECT, but that narrows it down to two rows only. I would really like to have just a one row result per organization.

Can anyone please help me?

Thanks,
MK

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2014-02-12 : 17:19:33

[code]
INSERT INTO Organization SW Reqs (Name, [Address], [Date], [SW1 Quantity], [SW2 Quantity], [SW3 Quantity])
SELECT t1.organization
,t1.[Address]
,t1.[Date]
,max(case when t2.[software type] = 'SW1' then t2.quantity end) as [SW1 Quantity]
,max(case when t2.[software type] = 'SW2' then t2.quantity end) as [SW2 Quantity]
,max(case when t2.[software type] = 'SW3' then t2.quantity end) as [SW3 Quantity]
from table1 t1
inner join table2 t2
on t2.organization = t1.organization
group by t1.organization
,t1.[Address]
,t1.[Date]
[/code]

if you have 2005 or greater you can also use PIVOT

Be One with the Optimizer
TG

mkdlmr
Starting Member

21 Posts

Posted - 2014-02-12 : 19:10:02

Thanks for the reply!

I am using Server 2008. Can you please tell me what the pivot method would look like?

Thanks,
MK

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources