Click here to Skip to main content
15,867,568 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
Hello Forum,
sorry for the uncommunicative Question title. But i don't know exactly how to describe.

The following is my Situation. I have a MS SQL Database with the table:

VIN | Description | ImagePath
123 | Fender | C:\123\Image1.jpg
123 | Fender | C:\123\Image2.jpg
123 | Fender | C:\123\Image3.jpg
124 | Hood | C:\124\Image1.jpg

So the description and vin are the same. But for that Damage i have 3 Pictures.

Now i would like to build a SQL Statement that list each descriptor once. Maybe anyone knows how to do this?

What I have tried:

select distinct * from BewertungMinder
where WENumber = '123'
Posted
Updated 20-Jan-19 23:07pm
Comments
Santosh kumar Pithani 21-Jan-19 4:50am    
Don't insert duplicate imagepath values "C:\123\Image3.jpg" on VIN column.Maintain primary key on table.
Sascha Manns 21-Jan-19 5:11am    
Hi Santosh,
thank you very much for your fast help. It works perfectly :-)
Greetings Sascha
Santosh kumar Pithani 21-Jan-19 5:32am    
Welcome..give me stars:-)
Sascha Manns 21-Jan-19 8:53am    
Done *thumbs up*

1 solution

SQL
CREATE TABLE #Temp(VIN INT, Description VARCHAR(10),ImagePath NVARCHAR(50));
INSERT INTO #Temp(VIN,Description,ImagePath)
VALUES
(123 ,'Fender','C:\123\Image1.jpg'),
(123 ,'Fender','C:\123\Image2.jpg'),
(123 ,'Fender','C:\123\Image3.jpg'),
(124 ,'Hood',' C:\124\Image1.jpg');

SELECT 
 DISTINCT VIN
 ,Description,
 STUFF(
        (select ','+ImagePath from #TEMP AS T1 
       where T1.VIN=T2.VIN FOR XML PATH(''),type
        ).value('.','NVARCHAR(100)'),1,1,'') AS ImagePath

FROM #TEMP AS T2;

OUTPUT:-
------------------------------------
VIN  | Description  | ImagePath
------------------------------------
123	Fender	  C:\123\Image1.jpg,C:\123\Image2.jpg,C:\123\Image3.jpg
124	Hood	  C:\124\Image1.jpg
 
Share this answer
 

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900