1. 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
    0

    Add a comment

Blog Archive
Topics
Topics
Loading
Dynamic Views theme. Powered by Blogger. Report Abuse.