sql server - T SQL: Select each matched row as column -

i'm new sql server , have problem

here baseline situation:

table foo:

id | pid  | val1 | val2 | val3 ------------------------------ 1  | 4721 |  1   |  2   |  3 2  |   25 |  4   |  5   |  6 3  | 4721 |  7   |  8   |  9 

result table:

pid | id_1 | val1_1 | val2_1 | val3_1 | id_2 | val1_2 | val2_2 | val3_2 | id_3 | val1_3 | val2_3 | val3_3 ---------------------------------------------------- 4721 | 1  |  1   |  2   |  3  | 2 | 4 | 5 | 6 | 3 | 7 | 8 | 9 

what want select matched rows pid = 4721, , show them in separate column.

strictly speaking, sql answer problem is, "this display issue. display application problem, not database problem." relationally speaking, asking server break first normal form , create repeating groups, meaning it's going require jumping through few hoops , have significant limitations.

the "right way" according dba doing like:

select pid, id, val1, val2, val3 table order pid, id; 

then, in application, walk through result set , format output need it.

you include order each id within pid maybe make bit easier:

select pid,      id,      val1,      val2,      val3,     row_number() on (partition pid, order id) "id_order" table order pid, id; 

however, let's can't that.

if absolutely have sql (e.g., reporting software doesn't handle kind of thing , it's you've got) , know never have more 3 id each pid, can try this:

;with table_id_ordered (     select pid,          id,          val1,          val2,          val3,         row_number() on (partition pid, order id) "id_order"     table ) select t1.pid,     t1.id   id_1,     t1.val1 val1_1,     t1.val2 val2_1,     t1.val3 val3_1,     t2.id   id_2,     t2.val1 val1_2,     t2.val2 val2_2,     t2.val3 val3_2,     t3.id   id_3,     t3.val1 val1_3,     t3.val2 val2_3,     t3.val3 val3_3 table_id_ordered t1 left join table_id_ordered t2     on t2.pid = t1.pid     , t2.id_order = t1.id_order + 1 left join table_id_ordered t3     on t3.pid = t2.pid     , t3.id_order = t2.id_order + 1 t1.id_order = 1; 

obviously, that's 3 id pid,. written, won't tell if there's id in table in fouth or fifth. they're absent results. first method mentioned return data, , application can written handle pretty easily.

dynamic solutions can created number of ids pid, more complicated.