July 22, 2015 at 1:22 pm
Hi
I am trying to "flatten" out multiple records into one line
SELECT Client_Teams.Parent, Teams.Name
FROM Client_Teams INNER JOIN
Teams ON Client_Teams.Team = Teams.ID
WHERE (Client_Teams.[End Date] IS NULL)
For example two teams parent and Team
Parent Team
001 Team1
001 TeamA
002 Team2
002 TeamB
I wanted my output to look like
Parent ATeam BTeam
001 Team1 TeamA
001 Team2 TeamB
Thanks
July 22, 2015 at 1:38 pm
You could use cross tabs to create by creating a row number for each team.
WITH CTE AS(
SELECT Client_Teams.Parent, Teams.Name,
ROW_NUMBER() OVER( PARTITION BY Client_Teams.Parent ORDER BY Teams.Name) rn
FROM Client_Teams INNER JOIN
Teams ON Client_Teams.Team = Teams.ID
WHERE (Client_Teams.[End Date] IS NULL)
)
SELECT Parent,
MAX(CASE WHEN rn = 1 THEN Name END) ATeam,
MAX(CASE WHEN rn = 2 THEN Name END) BTeam
--Add as many as you need
FROM CTE
GROUP BY Parent;
Or if you have exactly 2 teams per parent, you can use max and min.
SELECT Client_Teams.Parent,
MAX( Teams.Name) ATeam,
MIN( Teams.Name) BTeam
FROM Client_Teams INNER JOIN
Teams ON Client_Teams.Team = Teams.ID
WHERE (Client_Teams.[End Date] IS NULL);
To make the first code dynamic, check the following article:
July 23, 2015 at 8:31 am
Thank you Luis it is exactly what I needed . I feel a bit embarrassed I didn't figure that out
I worked in a case statement so any team with the word "Team" in it came out on TeamA, else null and if they had a second team it came out in TeamB
But Thanks Again
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply