Link to home
Start Free TrialLog in
Avatar of mlowe71
mlowe71

asked on

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
Avatar of Kyle Abrahams, PMP
Kyle Abrahams, PMP
Flag of United States of America image

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

What rdbms and version are you using?
I guess the better question, lol.  I always default to SQL server 2008 + when I see "sql"
Avatar of mlowe71
mlowe71

ASKER

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
Avatar of mlowe71

ASKER

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
ASKER CERTIFIED SOLUTION
Avatar of Kyle Abrahams, PMP
Kyle Abrahams, PMP
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of mlowe71

ASKER

Kyle, thank you so much you help is so much apprechiated