c# - 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 gridview show:

row 1: john doe        bla1 row 2: billy jones     bla2 

currently shows: (but dont want this):

row 1: john      bla1 row 2: doe       bla1 row 3: billy     bla2 row 4: jones     bla2 

i think have tried , use help! thanks.

select  [firstname],[lastname], question         (             select  a.question, b.[key], b.[value]                table1                     inner join table2 b                         on a.customerid = b.customerid         ) org         pivot         (             max([value])             [key] in ([firstname],[lastname])         ) pvt 

• sqlfiddle demo

output

╔═══════════╦══════════╦══════════╗ ║ firstname ║ lastname ║ question ║ ╠═══════════╬══════════╬══════════╣ ║ john      ║ doe      ║ bla1     ║ ║ billy     ║ jones    ║ bla2     ║ ╚═══════════╩══════════╩══════════╝