CODEWITHSUNDEEP
c#asp.netsqlsql-server-2008pivot
mlg74
mlg74

Reputation: 520

Combine gridview rows and eliminate duplicates

I have 2 tables:

Table 1

id   question   customerId
1     bla1         1
2     bla2         2    

Table 2

id    customerId    Key         Value
1      1           firstname    John
2      1           lastname     Doe
3      2           firstname    Billy
4      2           lastname     Jones

I need to get my gridview to show:

Row 1: John Doe        Bla1
Row 2: Billy Jones     Bla2

Currently it shows: (but I dont want it like this):

Row 1: John      Bla1
Row 2: Doe       Bla1
Row 3: Billy     Bla2
Row 4: Jones     Bla2

I think I have tried everything and could use your help! Thanks.

Upvotes: 3

Views: 658

Answers (3)

TechDo
TechDo

Reputation: 18629

Select the data for binding grid using below query:

SELECT 
    a.id, 
    a.Question,
    (SELECT Value + ' ' FROM Table2 b WHERE b.customerId=a.customerid 
        FOR XML PATH(''),type).value('.','nvarchar(max)') Value
FROM Table1 a

Upvotes: 0

Patrick D'Souza
Patrick D'Souza

Reputation: 3573

SELECT [firstname],[lastname], question
FROM
(
    SELECT Q.ID, Question, Q.CUSTOMERID, KeyId, KeyValue 
    FROM QUESTION Q
            INNER JOIN CUSTOMERMAP C ON Q.CUSTOMERID=C.CUSTOMERID
) as Data
PIVOT
(
   MAX(KeyValue)
   FOR KeyId in ([firstname],[lastname])
) as PT;

Upvotes: 0

John Woo
John Woo

Reputation: 263723

SELECT  [firstname],[lastname], question
FROM
        (
            SELECT  a.question, b.[key], b.[value]
            FROM    Table1 a
                    INNER JOIN Table2 b
                        ON a.customerID = b.CustomerID
        ) org
        PIVOT
        (
            MAX([value])
            FOR [KEY] IN ([firstname],[lastname])
        ) pvt

OUTPUT

╔═══════════╦══════════╦══════════╗
║ FIRSTNAME ║ LASTNAME ║ QUESTION ║
╠═══════════╬══════════╬══════════╣
║ John      ║ Doe      ║ bla1     ║
║ Billy     ║ Jones    ║ bla2     ║
╚═══════════╩══════════╩══════════╝

Upvotes: 3

Related Questions