Lets say we have two tables
Property Table:
PropID | Property | Value |
1 | aaa | 111 |
2 | bbb | 222 |
3 | ccc | 333 |
4 | ddd | 444 |
5 | eee | 555 |
6 | fff | 666 |
And user table:
UserID | Name | PropID |
1 | User1 | 1 |
1 | User1 | 2 |
1 | User1 | 3 |
2 | User2 | 3 |
2 | User2 | 4 |
3 | User3 | 5 |
4 | User4 | 6 |
We need results in the format:
1 | User1 | 111:222:333 |
2 | User2 | 333:444 |
3 | User3 | 555 |
4 | User4 | 666 |
DECLARE @tblProperties table (PropID int, Property varchar(20), Value varchar(20))
DECLARE @tblUsers table (UserID int, Name varchar(20), PropID Int)
INSERT @tblProperties (PropID, Property, Value)
SELECT 1, 'aaa', '111'
UNION ALL
SELECT 2, 'bbb', '222'
UNION ALL
SELECT 3, 'ccc', '333'
UNION ALL
SELECT 4, 'ddd', '444'
UNION ALL
SELECT 5, 'eee', '555'
UNION ALL
SELECT 6, 'fff', '666'
INSERT @tblUsers (UserID, Name, PropID)
SELECT 1, 'User1', 1
UNION ALL
SELECT 1, 'User1', 2
UNION ALL
SELECT 1, 'User1', 3
UNION ALL
SELECT 2, 'User2', 3
UNION ALL
SELECT 2, 'User2', 4
UNION ALL
SELECT 3, 'User3', 5
UNION ALL
SELECT 4, 'User4', 6
CREATE TABLE #TempTable(ID int, Name varchar(20), Property varchar(20), Value varchar(20) )
CREATE TABLE #TempTable2(ID int, Value varchar(20) )
--INSERT The combined data into first temp table.
INSERT INTO #TempTable
SELECT t.UserID, t.Name, p.Property, p.Value FROM @tblProperties p, @tblUsers t
WHERE t.PropID = p.PropID
--Merge the data where user id are same - seperate the values by ":"
INSERT INTO #TempTable2
SELECT distinct (tTable.ID),
Left(tTable.Value,Len(tTable.Value)-1)
AS [Values]
FROM(SELECT t2.ID,
(SELECT t1.Value + ':' AS [text()]
FROM #TempTable t1
Where t1.ID = t2.ID
ORDER BY t1.ID
For XML PATH ('')) [Value]
FROM #TempTable t2) [tTable]
--Get the result by combining the two temp tables.
SELECT DISTINCT(t2.ID), t1.Name, t2.Value FROM #TempTable2 t2, #TempTable t1
WHERE t1.ID = t2.ID
--Finally drop the temp tables.
DROP TABLE #TempTable
DROP TABLE #TempTable2
Add a comment