How to inner join drop mutiple rows to one

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)

How to inner join drop mutiple rows to one

Author

Topic

jrisinger
Starting Member

4 Posts

Posted - 2006-02-01 : 11:53:47

trying to do a inner join but its not working and im not sure if this will work to create new temp table.
example of what i am trying to get in the temp table and the RESULTS NEEDED - what i have wrote follows:

IF OBJECT_ID('tempdb..#temp_nametable') IS NOT NULL --use temporary table
DROP TABLE #temp_nametable
GO

SELECT a.uslid,
a.groupid,
--b.uslid,
b.username,
b.location,
CASE groupid
WHEN 'ADMIN' THEN 'ADMIN'
else ''
END AS group1,
CASE groupid
WHEN 'AP ADM' THEN 'AP ADM'
else ''
END AS group2,
CASE groupid
WHEN 'CS ADM' THEN 'CS ADM'
else ''
END AS group3,
CASE groupid
WHEN 'DIRECTORS' THEN 'DIRECTORS'
else ''
END AS group4,
CASE groupid
WHEN 'GL ADM' THEN 'GL ADM'
else ''
END AS group5,
CASE groupid
WHEN 'PA ADM' THEN 'PA ADM'
else ''
END AS group6,
CASE groupid
WHEN 'PA USERS' THEN 'PA USERS'
else ''
END AS group7,
CASE groupid
WHEN 'PO ADM' THEN 'PO ADM'
else ''
END AS group8,
CASE groupid
WHEN 'PQ ADM' THEN 'PQ ADM'
else ''
END AS group9,
CASE groupid
WHEN 'PQ REQUESTS' THEN 'PQ REQUESTS'
else ''
END AS group10
INTO #temp_nametable
FROM USLMBR a, USLUSR b
WHERE a.uslid = b.uslid

SELECT DISTINCT A.uslid,
A.username,
A.group1,
A.group2,
A.group3,
A.group4,
A.group5,
A.group6,
A.group7,
A.group8,
A.group9,
A.group10
FROM #temp_nametable AS A
INNER JOIN
#temp_nametable AS B
ON A.uslid = B.uslid
and A.groupid = B.groupid
--test by getting only one id
AND A.uslid = 'EZ'

results:


uslid username    group1 group2 group3 group4    group5 group6 group7   group8 group9 group10     
----- ----------- ------ ------ ------ --------- ------ ------ -------- ------ ------ ----------- 
EZ    Elouise Za                                                                      PQ REQUESTS
EZ    Elouise Za                                                               PO ADM
EZ    Elouise Za                                               PA ADM                        
EZ    Elouise Za                                        GL ADM                               
EZ    Elouise Za                       CS ADM                                                
EZ    Elouise Za                AP ADM

(6 row(s) affected)

RESULTS NEEDED:


uslid username    group1 group2 group3 group4    group5 group6 group7   group8 group9 group10     
----- ----------- ------ ------ ------ --------- ------ ------ -------- ------ ------ ----------- 
EZ    Elouise Za                AP ADM CS ADM           GL ADM PA ADM          PO ADM PQ REQUESTS

Hope i explained ok and results can be viewed, thank you in advance.

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2006-02-01 : 12:01:47

What happens if someone is in 11 groups? Why do you need the data returned in this format? It is so you can present it somewhere in your app or webpage all in 1 nice, neat row?

You should return raw data back to your client, and your client app should be able to easily format the data as needed. Forcing SQL Server to do this is very inefficient and really complicates matters.

jrisinger
Starting Member

4 Posts

Posted - 2006-02-01 : 12:24:53

There are 10 groups and if someone is in all ten then results desired are one line


uslid username group1 group2 group3 group4 group5 group6 group7 group8 group9 group10
EZ    Elouise  AA     AB     AP     CS     GG     GL     PA     PP     PO     PQ

Need this format to create reporting and/or changing group application ownerships...
Data is already in app and in production. hope this helps.

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2006-02-01 : 14:05:57

What tool are you using to create your reporting? Almost all report tools have the ability to crosstab.

if you want to do it in T-SQL (not recommended) you can do it like this:

select uslid, username,
max(case when groupid='AA' then 'AA' else Null END) as Group1,
max(case when groupid='AB' then 'AB' else Null END) as Group2,
max(case when groupid='AP' then 'AP' else Null END) as Group3,
.. etc...
from
...
group by uslid, username

jrisinger
Starting Member

4 Posts

Posted - 2006-02-01 : 17:52:20

Using T-SQL for reporting revised code.
Thank you Dr. Cross Join, your reply worked (and noted the not recommended).

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources