Select multiple values from same column and make them to show in a row

Question

Select multiple values from same column and make them to show in a row

kkbear

SSC-Addicted

Points: 457
More actions
June 10, 2015 at 1:48 pm

#304881

I created a query that got the following result. But I expect to get the structure like, care_nbr, cust_nbr,legal_name, address_type=physical address, addr_line_1, addr_line_2, address_type-primary address, ddr_line_1, addr_line_2. That means I only need primary and physical address, and expect them to show in a row to each care_nbr. How to perform that? Thank you.:-)
CARE_Nbr||Cust_Nbr||Legal_Name||||||| Address_Tpye |||Addr_Line_1 ||||||||||||||||Addr_Line_2
99000001||004554||Mac Marketing, LLC||Billing Address||210 Parktowne Blvd Ste. 1||NULL
99000001||004554||Mac Marketing, LLC||Mailing Address||210 Parktowne Blvd Ste. 1||NULL
99000001||004554||Mac Marketing, LLC||Primary Address||210 Parktowne Blvd Ste. 1||NULL
99000001||004554||Mac Marketing, LLC||Physical Address||210 Parktowne Blvd Suite 1||NULL
My script is here, and the sample result is attached. How should I modify this query to get my expected result?
select a.CARE_Number,
a.Customer_Nbr_Txt,
a.Customer_Type_Txt,
a.Legal_Name_Txt,
c.Address_Type_Txt,c.Address_Line_1_Txt,c.Address_Line_2_Txt,
a.Customer_Status_Txt,
a.Legal_Entity_Type_Txt,
a.Business_Unit_Txt
FROM dw_mart.dbo.DimCustomer a
join DW_ODS.dbo.Customer_Header_Tbl b
on a.Customer_Nbr_Txt=b.Customer_Nbr_Txt
join dw_ods.dbo.Customer_Address_Tbl c
on b.Cust_Nbr_Key=c.Cust_Nbr_Key
where (a.Legal_Name_Txt like '% LLC%'
or a.Legal_Name_Txt like '%L.L.C%'
or a.Legal_Name_Txt like '%L.C%')
and a.Legal_Entity_Struct_Txt not like '%Limited Liability%'
AND a.Current_Row_Flag='Y'
and a.Source_System_Code_Txt='WMS'
order by 1

Viewing 7 posts - 1 through 6 (of 6 total)

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

Alan Burstein SSC Guru Points: 61141 More actions · Answer 1

Can how post some DDL, sample data and the query you used to get the result set that you posted?

"I cant stress enough the importance of switching from a sequential files mindset to set-based thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code."

-- Itzik Ben-Gan 2001

kkbear SSC-Addicted Points: 457 More actions · Answer 2

🙂 already updated my query. What can I do to get expected structure? Thank you:-)

Jacob Wilkins One Orange Chip Points: 27976 More actions · Answer 3

As Alan pointed out, we'd be best able to help you if you posted DDL for the tables involved, sample data in the form of INSERTs, and desired results from the sample data provided.

His signature has a link to this article: http://www.sqlservercentral.com/articles/Best+Practices/61537/, which should help with that.

Without that, we can only really guess at what might work for you. Having said that, what you're wanting to do here sounds like a PIVOT. The below uses a CTE to generate the sample data you suggested in the first post, and then a second to get a full address (address lines 1 and 2, because you can't use expressions in the PIVOT clause).

From there's it's a normal PIVOT.

WITH CTE AS (

SELECT CARE_Nbr=99000001, Cust_Nbr=004554,Legal_Name='Mac Marketing, LLC',

Address_Type='Billing Address', Addr_Line_1='210 Parktowne Blvd Ste. 1', Addr_Line_2=NULL

UNION ALL

SELECT CARE_Nbr=99000001, Cust_Nbr=004554,Legal_Name='Mac Marketing, LLC',

Address_Type='Mailing Address', Addr_Line_1='210 Parktowne Blvd Ste. 1', Addr_Line_2=NULL

UNION ALL

SELECT CARE_Nbr=99000001, Cust_Nbr=004554,Legal_Name='Mac Marketing, LLC',

Address_Type='Primary Address', Addr_Line_1='210 Parktowne Blvd Ste. 1', Addr_Line_2=NULL

UNION ALL

SELECT CARE_Nbr=99000001, Cust_Nbr=004554,Legal_Name='Mac Marketing, LLC',

Address_Type='Physical Address', Addr_Line_1='210 Parktowne Blvd Suite 1', Addr_Line_2=NULL

)

,

CTE2 AS (

SELECT CARE_Nbr,Cust_Nbr,Legal_Name, Address_Type,

FullAddress=ISNULL(Addr_Line_1,'')+','+ISNULL(Addr_Line_2,'')

FROM CTE

)

SELECT * FROM CTE2

PIVOT (MAX(FullAddress) FOR [Address_Type] IN ([Primary Address],[Physical Address])) pvt

It works like I'm guessing you want it to on those rows, but again, I'm not sure what your actual data looks like.

If you can follow the guide in that article, it'll help us get you a better solution more quickly.

Cheers!

SQL.AP Hall of Fame Points: 3244 More actions · Answer 4

Use For XML.

____________________________________________________________

AP

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 5

-- It's not clear what you want as your output because you decribed it (badly)

-- rather than presenting it as data.

-- This might be correct, then again, it might not:

SELECT

c.CARE_Number,

c.Customer_Nbr_Txt,

c.Customer_Type_Txt,

c.Legal_Name_Txt,

a.Address_Type_Txt, a.Address_Line_1_Txt, a.Address_Line_2_Txt,

c.Customer_Status_Txt,

c.Legal_Entity_Type_Txt,

c.Business_Unit_Txt

FROM dw_mart.dbo.DimCustomer c

INNER JOIN DW_ODS.dbo.Customer_Header_Tbl h

ON c.Customer_Nbr_Txt = h.Customer_Nbr_Txt

left JOIN dw_ods.dbo.Customer_Address_Tbl a

ON h.Cust_Nbr_Key = a.Cust_Nbr_Key

AND a.Address_Type_Txt IN ('physical address', 'primary address')

WHERE (c.Legal_Name_Txt LIKE '% LLC%'

OR c.Legal_Name_Txt LIKE '%L.L.C%'

OR c.Legal_Name_Txt LIKE '%L.C%')

AND c.Legal_Entity_Struct_Txt NOT LIKE '%Limited Liability%'

AND c.Current_Row_Flag='Y'

AND c.Source_System_Code_Txt='WMS'

ORDER BY c.CARE_Number

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

kkbear SSC-Addicted Points: 457 More actions · Answer 6

kkbear

SSC-Addicted

Points: 457

June 11, 2015 at 8:04 am

#1804700

Thank you. 🙂