Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations John Tel on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Pull record from multiple rows 4

Status
Not open for further replies.
dk2006

dk2006

MIS
Jan 19, 2006
53
US
Hi all,
I feel stupid for not knowing how to do this though it might be very simple to do.

table I have

ID Name AddressType Address
1 John Business 123 Test Dr
1 John Home 345 Jackson St
2 Bart Business PO Box 123
2 Bart Home 1234 Low Dr

I want a query to return

ID Name BusinessAddress HomeAddress
1 John 123 Test Dr 345 Jackson St
2 Bart PO Box 123 1234 Low Dr

Please help

Thank you,

Sam
 
Sort by date Sort by votes
Try this:

Code: 
SELECT ID, NAME, 
(SELECT Address FROM MyTBL T2 Where T1.ID = T2.ID and T1.Name = T2.name and AddressType = 'BUSINESS') BusinessAddress,
(SELECT Address FROM MyTBL T2 Where T1.ID = T2.ID and T1.Name = T2.name and AddressType = 'Home') HomeAddress
FROM MyTBL T1
GROUP BY ID, NAME

Sunil
 
Upvote 0 Downvote
Code: 
SELECT Id, Name,
       CASE WHEN AddressType = 'Business'
            THEN Address
            ELSE '' END AS BusinessAddress,
       CASE WHEN AddressType = 'Home'
            THEN Address
            ELSE '' END AS HomeAddress
FROM MyTable
GROUP BY Id, Name

Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.
MVP VFP
 
Upvote 0 Downvote
Thank you Sunil. The only problem I have now it display duplicated records. For example it would return...


ID Name BusinessAddress HomeAddress
1 John 123 Test Dr 345 Jackson St
1 John 123 Test Dr 345 Jackson St
2 Bart PO Box 123 1234 Low Dr
2 Bart PO Box 123 1234 Low Dr

 
Upvote 0 Downvote
Boris almost had it. [wink]

He forgot the min

Code: 
Select Id, 
       Name,
       [!]Min([/!]Case When AddressType = 'Business' Then Address End[!])[/!] As BusinessAddress,
       [!]Min([/!]Case When AddressType = 'Home' Then Address End[!])[/!] As HomeAddress
From 	@Temp
Group By Id, Name

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom
 
Upvote 0 Downvote
F....
Always forgot that I MUST add all fields (not involved in agregate functions) in GROUP BY.
(Bad habbit from older versions of VFP)

Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.
MVP VFP
 
Upvote 0 Downvote
Borislav's way doesn't give me what I am looking for. In fact it will return something like this

ID Name BusinessAddress HomeAddress
1 John 123 Test Dr NULL
1 John NULL 345 Jackson St
2 Bart PO Box 123 NULL
2 Bart NULL 1234 Low Dr

Sunil's way almost give me what I'm looking for. It return duplication records like...

ID Name BusinessAddress HomeAddress
1 John 123 Test Dr 345 Jackson St
1 John 123 Test Dr 345 Jackson St
2 Bart PO Box 123 1234 Low Dr
2 Bart PO Box 123 1234 Low Dr

 
Upvote 0 Downvote
try my method (with the mins). Change the @Temp to your table name.


-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom
 
Upvote 0 Downvote
George,
Your suggestion is very similar to Borislav's where it will return..
ID Name BusinessAddress HomeAddress
1 John 123 Test Dr NULL
1 John NULL 345 Jackson St
2 Bart PO Box 123 NULL
2 Bart NULL 1234 Low Dr


 
Upvote 0 Downvote
Nope. Mine works. You must have implemented it wrong, or there must be more to the query. Here's how I tested it.

Code: 
[green]-- Setup a test table (as a table variable)[/green]
Declare @Temp Table(id Integer, Name VarChar(50), AddressType VarChar(50), Address VarChar(50))

Insert Into @Temp Values(1,'John','Business','123 Test Dr')
Insert Into @Temp Values(1,'John','Home','345 Jackson St')
Insert Into @Temp Values(2,'Bart','Business','PO Box 123')
Insert Into @Temp Values(2,'Bart','Home','1234 Low Dr')

[green]-- here's the query[/green]
Select Id, Name,
       Min(Case When AddressType = 'Business' Then Address End) As BusinessAddress,
       Min(Case When AddressType = 'Home' Then Address End) As HomeAddress
From   @Temp
Group By Id, Name

You can copy/paste this to query analyzer to see that it works.

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom
 
Upvote 0 Downvote
NO WAY!
Code: 
DECLARE @Temp TABLE (Id int, Name varchar(20), AddressType varchar(50), Address varchar(200))
INSERT INTO @Temp VALUES (1, 'John','Business','123 Test Dr')
INSERT INTO @Temp VALUES (1, 'John','Home','345 Jackson St')
INSERT INTO @Temp VALUES (2, 'Bart','Business','PO Box 123')
INSERT INTO @Temp VALUES (2, 'Bart','Home','1234 Low Dr')

Select Id,
       Name,
       Min(Case When AddressType = 'Business' Then Address End) As BusinessAddress,
       Min(Case When AddressType = 'Home' Then Address End) As HomeAddress
From     @Temp
Group By Id, Name

Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.
MVP VFP
 
Upvote 0 Downvote
And the results are:

[tt][blue]
Id Name BusinessAddress HomeAddress
---- -------- --------------- ------------
2 Bart PO Box 123 1234 Low Dr
1 John 123 Test Dr 345 Jackson St
[/blue][/tt]

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom
 
Upvote 0 Downvote
Oops!
Sorry George, I didn't read your post before I made mine :-(

Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.
MVP VFP
 
Upvote 0 Downvote
It was only a minute apart, so mine wasn't there when you posted yours. [smile]

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom
 
Upvote 0 Downvote
First of all George and Borislav, you are right. You both deserve stars.
for my problem, I can't seem to apply your method. Here's exact script I try to do

Code: 
Declare 	@Table_3 TABLE([Org_ID] Integer)
DECLARE	@Region_Desc				VARCHAR(50)
DECLARE	@OrgStatus_Desc				VARCHAR(50)
DECLARE @AddressType				VARCHAR(50)


SELECT @Region_Desc = LOWER(LTRIM(RTRIM(Region_Desc))) FROM Region WHERE Region_ID = 1
SELECT @OrgStatus_Desc = LOWER(LTRIM(RTRIM(OrgStatus_Desc))) FROM OrgStatus WHERE OrgStatus_ID = 1

INSERT INTO 	@Table_3
SELECT 	Org_ID
FROM 		View_Orgs3
WHERE		LOWER(LTRIM(RTRIM(Region_Desc))) LIKE @Region_Desc AND
		LOWER(LTRIM(RTRIM(ContactType_Desc))) LIKE 'mail-to' AND 	
		OrgStatus_Desc LIKE @OrgStatus_Desc AND
		[green]--(dbo.fn_CalcPaintLineValueByOrgID(1, GETDATE(), View_Orgs3.Org_ID)) <> 0[/green]
		ColorDoc_Org_ID <> 0
		
GROUP BY	Org_ID

SELECT		vw1.Org_ID,
		Org_LegalName, 

		MIN( CASE WHEN ContactMethod_Desc = 'Postal' AND ContactType_Desc='Business' THEN Address_Street END) AS Business_Address,
		MIN( CASE WHEN ContactMethod_Desc = 'Postal' AND ContactType_Desc='Mail-To' THEN Address_Street END) AS MailTo_Address,

		Address_Street,
		Address_City, 
		ColorDoc_Org_ID,
		Address_StateCode, 
		ContactType_Desc, 
		ContactMethod_Desc

FROM 		View_Orgs3 vw1
WHERE		vw1.Org_ID IN (SELECT Org_ID FROM @Table_3) AND
 		ContactMethod_Desc = 'Postal' AND
		(ContactType_Desc='Mail-to' OR ContactType_Desc='Business')

GROUP BY	Org_ID,Org_LegalName,Address_Street,Address_city,ColorDoc_Org_ID,Address_StateCode,ContactType_Desc, ContactMethod_Desc

What did i do wrong here?

Thanks,
sam
 
Upvote 0 Downvote
Change the last part of your query as below and see if that helps.
Code: 
SELECT        vw1.Org_ID,
        Org_LegalName,

        MIN( CASE WHEN ContactMethod_Desc = 'Postal' AND ContactType_Desc='Business' THEN Address_Street END) AS Business_Address,
        MIN( CASE WHEN ContactMethod_Desc = 'Postal' AND ContactType_Desc='Mail-To' THEN Address_Street END) AS MailTo_Address,

        MIN(Address_Street) Address_Street,
        MIN(Address_City) Address_City,
        MIN(ColorDoc_Org_ID) ColorDoc_Org_ID,
        MIN(Address_StateCode) Address_StateCode,
        MIN(ContactType_Desc) ContactType_Desc,
        MIN(ContactMethod_Desc) ContactMethod_Desc

FROM         View_Orgs3 vw1
WHERE        vw1.Org_ID IN (SELECT Org_ID FROM @Table_3) AND
         ContactMethod_Desc = 'Postal' AND
        (ContactType_Desc='Mail-to' OR ContactType_Desc='Business')

GROUP BY    Org_ID,Org_LegalName

Sunil
 
Upvote 0 Downvote
Sunil,
No, it won't help because it is not contained in either an aggregate function or the GROUP BY clause.

sam
 
Upvote 0 Downvote
Try this...

Code: 
SELECT  vw1.Org_ID,
        Org_LegalName, 
        A.Business_Address,
        A.Business_City,
        A.Business_StateCode,
        A.MailTo_Address,
        A.MailTo_City,
        A.MailTo_StateCode,
        ColorDoc_Org_ID,
        ContactType_Desc, 
        ContactMethod_Desc

FROM    View_Orgs3 vw1
        Inner Join 
        (
        Select  Org_ID,
                MIN( CASE WHEN ContactMethod_Desc = 'Postal' AND ContactType_Desc='Business' THEN Address_Street END) AS Business_Address,
                MIN( CASE WHEN ContactMethod_Desc = 'Postal' AND ContactType_Desc='Business' THEN Address_City END) AS Business_City,
                MIN( CASE WHEN ContactMethod_Desc = 'Postal' AND ContactType_Desc='Business' THEN Address_StateCode END) AS Business_StateCode,
        
                MIN( CASE WHEN ContactMethod_Desc = 'Postal' AND ContactType_Desc='Mail-To' THEN Address_Street END) AS MailTo_Address,
                MIN( CASE WHEN ContactMethod_Desc = 'Postal' AND ContactType_Desc='Mail-To' THEN Address_City END) AS MailTo_City,
                MIN( CASE WHEN ContactMethod_Desc = 'Postal' AND ContactType_Desc='Mail-To' THEN Address_StateCode END) AS MailTo_StateCode
        From    View_Orgs3
        Where   ContactMethod_Desc = 'Postal' AND
                ContactType_Desc In ('Mail-to','Business')
        Group By Org_Id
        ) As A
          On vw1.Org_Id = A.Org_Id

WHERE   vw1.Org_ID IN (SELECT Org_ID FROM @Table_3) AND
        ContactMethod_Desc = 'Postal' AND
        ContactType_Desc In ('Mail-to','Business')

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom
 
Upvote 0 Downvote
Why I think this is enough (not surem becuase I can't test it). No declarations, no temp tables are involved, but as i said you should test it carefully:
Code: 
SELECT  vw1.Org_ID,
        Org_LegalName,
        MIN( CASE WHEN ContactMethod_Desc = 'Postal' AND ContactType_Desc='Business'
                  THEN Address_Street END) AS Business_Address,
        MIN( CASE WHEN ContactMethod_Desc = 'Postal' AND ContactType_Desc='Mail-To'
                  THEN Address_Street END) AS MailTo_Address,
        MIN(Address_Street)     AS Address_Street,
        MIN(Address_City)       AS Address_City,
        MIN(ColorDoc_Org_ID)    AS ColorDoc_Org_ID,
        MIN(Address_StateCode)  AS Address_StateCode,
        MIN(ContactType_Desc)   AS ContactType_Desc,
        MIN(ContactMethod_Desc) AS ContactMethod_Desc

FROM   View_Orgs3
INNER JOIN Region    ON View_Orgs3.Region_Desc    = Region.Region_Desc AND Region_Id = 1
INNER JOIN OrgStatus ON View_Orgs3.OrgStatus_Desc = OrgStatus.OrgStatus_Desc AND OrgStatus = 1
WHERE ContactMethod_Desc = 'Postal' AND
       (ContactType_Desc='Mail-to' OR ContactType_Desc='Business')
GROUP BY Org_ID,Org_LegalName

Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.
MVP VFP
 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

SashaBuilder3
  • Locked
  • Question
select a single row based on conditions in multiple rows 1
Replies
3
Views
804
SashaBuilder3
SashaBuilder3
peac3
  • Locked
  • Question
Consolidate multiple rows into single row 1
Replies
4
Views
1K
Chris Miller
Chris Miller
peac3
  • Locked
  • Question
split one row into multiple rows... help pls 2
Replies
12
Views
1K
Chris Miller
Chris Miller
bharons
  • Locked
  • Question
Pick up value from combobox
Replies
0
Views
551
bharons
bharons
Brianfree
  • Locked
  • Question
return unique rows only
Replies
11
Views
515
Brianfree
Brianfree

Part and Inventory Search

Sponsor

Back
Top