Single line of result set from multiple rows of t- sql data

I need to include multiple rows of data in SQL Database onto a single row. Example

Result set
ID        Keycode
1          65056825
1          12121212


Desired Result Set
ID         Keycode1       Keycode 2
1           65056825       12121212
mlowe71Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Kyle AbrahamsSenior .Net DeveloperCommented:
you can accomplish that with a cte, row_number() and left joins:
;with CTE as
(
  select id, keycode, row_number() over (partition by id order by keycode) rn 
  from <table>
)

select c1.id, c1.keycode, c2.KeyCode
from cte c1
left join cte c2 on c1.id = c2.id and rn = 2
-- wash, rinse repeat for max count of keycodes
where 
c1.rn = 1

Open in new window

0
awking00Commented:
What rdbms and version are you using?
1
Kyle AbrahamsSenior .Net DeveloperCommented:
I guess the better question, lol.  I always default to SQL server 2008 + when I see "sql"
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

mlowe71Author Commented:
Hi Kyle

Thank you for your quick response. I'm not sure if a Typo but im getting Ambiguous column name ' rn 'when i copy and paste your solution
0
mlowe71Author Commented:
HI Again

Kyle, I amended your sql to read  < and c2.rn  = 2 >  which works perfectly....

How would i organize the sql if the number of keycodes was more than 2. Example

Result set
ID        Keycode
1          65056825
1          12121212
1           45454545
1           66666666


Desired Result Set
ID         Keycode1       Keycode 2     Keycode 3    Keycode 4
1           65056825       12121212     45454545     66666666


Thank you so much in advance
0
Kyle AbrahamsSenior .Net DeveloperCommented:
you would need to add a join for each column:
;with CTE as
(
  select id, keycode, row_number() over (partition by id order by keycode) rn 
  from <table>
)

select c1.id, c1.keycode KeyCode1 , c2.KeyCode KeyCode2, c3.KeyCode KeyCode3 --repeat for more keaycodes
from cte c1
left join cte c2 on c1.id = c2.id and c2.rn = 2
left join cte c3 on c1.id = c3.id and c3.rn = 3  --copy this line change 3 to  4 or whatever.
-- wash, rinse repeat for max count of keycodes
where 
c1.rn = 1

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
mlowe71Author Commented:
Kyle, thank you so much you help is so much apprechiated
1
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
SQL

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.