SQL, same column different rows to same row different columns

Question

I am trying to make this happen with SQL.

Table looks like this:

SerialNo    part    location
12345       aaa     R1
12345       bbb     R2
67890       ccc     R3
67890       ddd     R4
12345       eee     R5

I want to get:

SerialNo    Part1   Location1   Part2   Location2   Part3   Location3
12345       aaa     R1          bbb     R2          eee     R5
67890       ccc     R3          ddd     R4

Can anyone help?

Questions asking for code must demonstrate a minimal understanding of the problem being solved. Include attempted solutions, and why they didn't work. — Kermit, Nov 19 '13 at 15:29
Sounds like you may need a pivot or crosstab of some sort. What version of sql server are you using? Check out the attached links below (since I couldn't format these in one comment); I would put this as an answer, but I haven't had too much testing experience to confirm. — Tiny Haitian, Nov 19 '13 at 15:30
http://stackoverflow.com/questions/24470/sql-server-pivot-examples — Tiny Haitian, Nov 19 '13 at 15:33
http://blogs.msdn.com/b/spike/archive/2009/03/03/pivot-tables-in-sql-server-a-simple-sample.aspx — Tiny Haitian, Nov 19 '13 at 15:33
http://www.mssqltips.com/sqlservertip/1019/crosstab-queries-using-pivot-in-sql-server/ — Tiny Haitian, Nov 19 '13 at 15:34
Sorry for that, I am a beginner here and for SQL, this is a part of a rapport I need build it from several tabels. I am using Atlantis SQL everywhere. Thanks! — user3009361, Nov 19 '13 at 16:05

score 2 · Answer 1 · answered Nov 19 '13 at 15:26

2

I would say it is impossible in SQL; and it is really bad style.

The main problem is that SQL has predefined count of columns, while your "table" hasn't - there is no upper limit.

So, normally, you would fetch your data ORDER BY sn and then use the executing script/program to format it accordingly.

To help you with that, we would need to know your desired scripting/programming language.

answered Nov 19 '13 at 15:26

Alexander

19,906
19
75
162

I wouldn't say impossible, as (per my comment above) I have seen it done with crosstabs and pivots. Bad style is questionable, but it was a royal pain when I first looked at it in SQL Server 2000. Maybe I just need more experience with it. LOL Pivots actually do help circumvent your 2nd statement. Whether they're great on a performance perspective: I don't know. – Tiny Haitian Nov 19 '13 at 15:41

score 0 · Accepted Answer · answered Nov 19 '13 at 15:37

you can try pivot. in my example here i have concatenated part and location together because i have no idea how to do this with two columns. the point that this is not good style remains, but maybe this helps you out a little.

i named your table ttt in my example:

select 'serialno' as serialno
     , [1], [2], [3], [4], [5]
from
( select ROW_NUMBER() over (partition by serialno order by part + ' - ' + location) as rn 
       , serialno
       , (part + ' - ' + location) as pl
    from ttt ) as sourcetable
  pivot ( 
    max(pl) 
    for rn in ([1], [2], [3], [4], [5])
    )  as pivottable

enter image description here

as you can see there i only provided five slots. you can add more if you like, but there will always be a fixed amount of them

SQL, same column different rows to same row different columns

2 Answers2

Linked