January 28, 2014 at 1:00 pm
I need a distinct list of customers, applications, and products but if there is more than one row for an application/product combination I want the system to return one row and a null customer_id.
I created a table variable to show all possible permutations of customer_id, application_id, and product_id for a specific address.
declare @customer_applications_products table
(
customer_id varchar(20),
application_id varchar (2),
product_id varchar(2)
)
insert @customer_applications_products
select
cap.custnmbr,
cap.application_id,
cap.product_id
from
addresses a
left join customers c on c.master_cust_no = a.builder_id
left join RM00101 r on r.CUSTNMBR = c.custnmbr
left join customer_applications_products cap on cap.custnmbr = c.custnmbr
where
address_id = 224744
and r.INACTIVE = 0
and c.is_customer = 1
and cap.application_id is not Null
This table contains the following data:
customer_id application_id product_id
MHI1957 1 1
MHIPO 1 1
MHIPO 1 2
MHIPO 1 3
MHIPO 1 6
MHIPO 11 6
MHIPO 11 O
MHIPO 12 B
MHIPO 2 O
MHIPO 3 6
MHIPO 4 4
MHIPO 4 O
If there are two rows with the same application_id and product_id, I want SQL to combine these rows into 1 row and change the customer_id to Null.
Is there a way for me to do this without using a cursor to go through each row?
CRC
January 28, 2014 at 1:12 pm
Maybe this would give you a clue on how to do it while you're inserting your data into your table variable. Why are you inserting into the table variable? Are you using the information later in your query?
WITH SampleData(customer_id, application_id, product_id) AS(
SELECT CAST( customer_id AS varchar(20)),
CAST( application_id AS varchar(2)),
CAST( product_id AS varchar(2))
FROM (VALUES
('MHI1957', 1, '1'),
('MHIPO', 1, '1'),
('MHIPO', 1, '2'),
('MHIPO', 1, '3'),
('MHIPO', 1, '6'),
('MHIPO', 11, '6'),
('MHIPO', 11, 'O'),
('MHIPO', 12, 'B'),
('MHIPO', 2, 'O'),
('MHIPO', 3, '6'),
('MHIPO', 4, '4'),
('MHIPO', 4, 'O'))x(customer_id, application_id, product_id)
)
SELECT CASE WHEN COUNT(*) = 1 THEN MAX(customer_id) END AS customer_id,
application_id,
product_id
FROM SampleData
GROUP BY application_id,
product_id
January 28, 2014 at 1:14 pm
Not entirely sure what you want here. Do you want only the first row to show customer_id and any subsequent rows to hide that value? Essentially providing a report style of output? I would recommend doing that in your front end.
Nice job posting ddl but your sample data is not very easy to use. Insert statements would make this a lot easier. Also, if you could more clearly explain what you want as output.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
January 28, 2014 at 1:17 pm
OK I read your post again and was horribly confused by the query in the middle until I realized that is what you used to fill your table variable.
Here is your ddl and sample data as I understand it.
declare @customer_applications_products table
(
customer_id varchar(20),
application_id varchar (2),
product_id varchar(2)
)
insert @customer_applications_products
select 'MHI1957', '1', '1' union all
select 'MHIPO', '1', '1' union all
select 'MHIPO', '1', '2' union all
select 'MHIPO', '1', '3' union all
select 'MHIPO', '1', '6' union all
select 'MHIPO', '11', '6' union all
select 'MHIPO', '11', 'O' union all
select 'MHIPO', '12', 'B' union all
select 'MHIPO', '2', 'O' union all
select 'MHIPO', '3', '6' union all
select 'MHIPO', '4', '4' union all
select 'MHIPO', '4', 'O'
select *
from @customer_applications_products
Assuming I am correct about replacing customer_id with NULL on subsequent rows you need to define how you want to sort these rows. What indicates "first"?
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
January 28, 2014 at 1:17 pm
Yes, I am placing it into a table variable for use later in my larger stored procedure.
The table you are seeing is what was inserted into @customer_applications_products from the select statement above it. This is not data that I am stuffing.
In the returned data shown because there are two rows for application_id = 1 and product_id = 1, I want only one row to be returned instead of 2 and the customer_id to be updated to Null.
CRC
January 28, 2014 at 1:36 pm
Sean thanks so much for your help. Based on your previous response I was able to figure out what I needed to do. I just needed to add the max() to my insert of my customer_id and everything worked after that.
CRC
January 28, 2014 at 1:40 pm
CRC (1/28/2014)
Sean thanks so much for your help. Based on your previous response I was able to figure out what I needed to do. I just needed to add the max() to my insert of my customer_id and everything worked after that.
Not entirely sure what you ended up with but I am glad that I was able to help you figure it out and thanks for letting us know.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply