using ms-sql looking best way construct conditional crosstab query guess best way describe it.
simplifying here, have dataset of clients (each unique id). each of these clients assigned team. clients assigned 1 team, can assigned 2, 3 or 5 teams. query results following:
client_id assigned_team1 assigned_team2 assigned_team3 assigned_team4 assigned_team5 --------- -------------- -------------- -------------- -------------- -------------- 87496 red team null null null null 74559 red team blue team white team null null 56345 blue team green team null null null 21473 yellow team blue team white team red team green team
in order result want use row_number(). row_number used assign sequential value each team client_id. sequential number used transform result columns. there few ways can convert rows of data columns. (note: guessing on table structure)
you can use aggregate function case expression:
select client_id, max(case when seq = 1 team end) assigned_team1, max(case when seq = 2 team end) assigned_team2, max(case when seq = 3 team end) assigned_team3, max(case when seq = 4 team end) assigned_team4, max(case when seq = 5 team end) assigned_team5 ( select client_id, team, row_number() over(partition client_id order team) seq clients ) d group client_id; see sql fiddle demo.
or can use pivot function:
select client_id, [1] assigned_team1, [2] assigned_team2, [3] assigned_team3, [4] assigned_team4, [5] assigned_team5 ( select client_id, team, row_number() over(partition client_id order team) seq clients ) d pivot ( max(team) seq in ([1], [2], [3], [4], [5]) ) piv; see sql fiddle demo
