SQL Server SSRS Multiple lookup values

Question

I have a huge set of data. Some pf the data has multiple values, kinda looking like this:

Column 1    Column 2
A           1
A           10
A           1E
B           2F
B           1BH
C           WBH
D           3X
D           2
D           1
D           10
D           11

I would like to select the unique values in Column 1 and display all relevant values of Column 2 in as string separated by comma (using SSRS). i.e.

Column 1    Column 2
A           01, 10, 1E
B           2F, 1BH
C           WBH
D           02, 01, 10, 11

In addition, any value in Column 1 that is less than 10, I would like it to be preceded by a zero.

I know I can use SELECT DISTINCT to get all unique values of Column 1. But I am unsure how to go around Column 2?

With regards to having a zero preceding numbers less than 10, I can do this:

SELECT RIGHT('0' + convert(varchar(2), value()), 2)

I am unsure how to put it all together to get the result I want.

Thank you.

Similar questions have been asked before. Check out the answers on [this question](https://stackoverflow.com/questions/194852/how-to-concatenate-text-from-multiple-rows-into-a-single-text-string-in-sql-serv) or [this question](https://stackoverflow.com/questions/15843649/tsql-for-xml-path-failing-to-group) for inspiration. — Sander, May 19 '18 at 09:47
what you are looking for is STUFF FOR XML in SQL.. padding with 0 is easy.. just look at length, and if 1 append a 0 else leave it alone.. then do the STUFF for XML.. google it.. there are thousands of examples. — Harry, May 20 '18 at 21:32

score 0 · Answer 1 · answered Jul 04 '18 at 02:36

I think this is what you want.

DECLARE @Input TABLE
(
ProductID INT,
Price INT
)

INSERT INTO @Input VALUES (6,22), (6,35), (6,77), (6, 88), (6,55),(6,200),(7,6),(7,4),(8,5),(8,5)

;WITH CTE AS
(SELECT ProductID, MAX(Price) AS Max_Price, MIN(Price) AS Min_Price 
FROM @Input 
GROUP BY ProductID
)
SELECT ProductID, CASE WHEN Max_Price > Min_Price THEN CONVERT(VARCHAR(10), Min_Price) + ', ' + CONVERT(VARCHAR(10),Max_Price)
                        ELSE CONVERT(VARCHAR(10), Min_Price) END AS Price_Range
FROM CTE

SQL Server SSRS Multiple lookup values

1 Answers1