Convert Row Data in 1 Field to Many Columns

Question

Convert Row Data in 1 Field to Many Columns

Japster

Grasshopper

Points: 23
More actions
April 2, 2014 at 3:09 am

#291895

Hello this is my first post and my TSQL is average at best so any help will be much appreciated
I want to create a view on a SQL 2005 Table that contains the data I need but the data is currently in the wrong layout.
What I mean is there are 2 fields (SerialBox, SerialNo) which currently have the format below :-
SerialBox SerialNo
Box1 Serial1
Box1 Serial2
Box1 Serial3
Box1 Serial4
Box1 Serial5
Box1 Serial6
Box1 Serial7
Box2 Serial1
Box2 Serial2
etc etc
What I want is
SerialBox SerialNo1 SerialNo2 SerialNo3 SerialNo4 SerialNo5 SerialNo6 SerialNo7
Box1 Serial1 Serial2 Serial3 Serial4 Serial5 Serial6 Serial7
Box2 Serial1 Serial2 Serial3 Serial4 Serial5 Serial6 Serial7
Box3 Serial1 Serial2 Serial3 Serial4 Serial5 Serial6 Serial7
Can this be done in a View ?
Thanks,
Craig

Viewing 14 posts - 1 through 13 (of 13 total)

You must be logged in to reply to this topic. Login to reply

pietlinden SSC Guru Points: 63503 More actions · Answer 1

Are you trying to create a crosstab of some type? Or are you trying to create something that looks like labels? Both are reasonably easy in SSRS.

You can create crosstabs in T-SQL... Jeff Moden posted examples in the Articles section... here's one:

Jeff Moden's Crosstabs Article[/url]

If you're using SSRS, you can use the Matrix wizard... although it's not clear what the non-first rows and columns represent from your example. What do they represent? In a crosstab, they're some kind of summary function: SUM, AVG, MIN, MAX... etc.

Cody Konior SSCarpal Tunnel Points: 4827 More actions · Answer 2

Cody Konior

SSCarpal Tunnel

Points: 4827

April 6, 2014 at 11:11 pm

#1703642

Are you looking for pivot/unpivot?

Jonathan AC Roberts SSCoach Points: 18356 More actions · Answer 3

I think this might be what you need:

;WITH CTE AS

(

SELECT DISTINCT

SerialBox

FROM Boxes B

),

CTE1 AS

(

SELECT CTE.SerialBox,

STUFF(A.SerialNos,1,1,'') SerialNos

FROM CTE

CROSS APPLY(SELECT ',' + B1.SerialNo

FROM Boxes B1

WHERE B1.SerialBox = CTE.SerialBox

FOR XML PATH ('')) AS A(SerialNos)

)

SELECT *

FROM CTE1

Japster Grasshopper Points: 23 More actions · Answer 4

Thanks for the information,

1st day back in work today so will take a look and let you know how I get on

J Livingston SQL SSC Guru Points: 51550 More actions · Answer 5

Japster (4/2/2014)
What I want is
SerialBox SerialNo1 SerialNo2 SerialNo3 SerialNo4 SerialNo5 SerialNo6 SerialNo7
Box1 Serial1 Serial2 Serial3 Serial4 Serial5 Serial6 Serial7
Box2 Serial1 Serial2 Serial3 Serial4 Serial5 Serial6 Serial7
Box3 Serial1 Serial2 Serial3 Serial4 Serial5 Serial6 Serial7
Can this be done in a View ?
Thanks,
Craig

were you expecting that each serialno was in a separate column?

am thinking you made to need to use dynamic SQL

________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day

Sean Lange SSC Guru Points: 286590 More actions · Answer 6

This looks like a cookie cutter cross tab to me. Please see the articles in my signature about cross tabs. Also, here is a link to another thread where I posted a solution to a very similar type of problem earlier today.

http://www.sqlservercentral.com/Forums/Topic1559371-392-1.aspx

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

Japster Grasshopper Points: 23 More actions · Answer 7

J Livingston SQL (4/8/2014)
Japster (4/2/2014)
What I want is
SerialBox SerialNo1 SerialNo2 SerialNo3 SerialNo4 SerialNo5 SerialNo6 SerialNo7
Box1 Serial1 Serial2 Serial3 Serial4 Serial5 Serial6 Serial7
Box2 Serial1 Serial2 Serial3 Serial4 Serial5 Serial6 Serial7
Box3 Serial1 Serial2 Serial3 Serial4 Serial5 Serial6 Serial7
Can this be done in a View ?
Thanks,
Craig
were you expecting that each serialno was in a separate column?
am thinking you made to need to use dynamic SQL

Yes was expecting the SerialNo's to be in seperate columns. I did post it that way in the original post but it's stripped out the spaces and squished it up

Thanks

Jonathan AC Roberts SSCoach Points: 18356 More actions · Answer 8

Is there a maximum number of distinct serial numbers or are there at most 7 like in your example?

Japster Grasshopper Points: 23 More actions · Answer 9

Hi,

thanks for the reply, for this exercise there will always be 7 serials per box.

However there is data in the table that has 42 serials in a box, will that cause a problem ?

Thanks

Sean Lange SSC Guru Points: 286590 More actions · Answer 10

Japster (4/9/2014)
Hi,
thanks for the reply, for this exercise there will always be 7 serials per box.
However there is data in the table that has 42 serials in a box, will that cause a problem ?
Thanks

Nope. Look at the example I linked yesterday. It will show exactly how to do this kind of thing.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

Jonathan AC Roberts SSCoach Points: 18356 More actions · Answer 11

Jonathan AC Roberts

SSCoach

Points: 18356

April 9, 2014 at 9:17 am

#1704570

So this is your homework?

Japster Grasshopper Points: 23 More actions · Answer 12

Sean Lange (4/9/2014)
Japster (4/9/2014)
Hi,
thanks for the reply, for this exercise there will always be 7 serials per box.
However there is data in the table that has 42 serials in a box, will that cause a problem ?
Thanks
Nope. Look at the example I linked yesterday. It will show exactly how to do this kind of thing.

Yes I looked at the link you provided yesterday and I still don't get it, sorry my SQL skills are weak :ermm:

Sean Lange SSC Guru Points: 286590 More actions · Answer 13

Japster (4/9/2014)
Sean Lange (4/9/2014)
Japster (4/9/2014)
Hi,
thanks for the reply, for this exercise there will always be 7 serials per box.
However there is data in the table that has 42 serials in a box, will that cause a problem ?
Thanks
Nope. Look at the example I linked yesterday. It will show exactly how to do this kind of thing.
Yes I looked at the link you provided yesterday and I still don't get it, sorry my SQL skills are weak :ermm:

OK. Then I am not going to post the code because you need to understand any code that you use. Let's break this down into pieces to help you understand what is going on.

The first step was to create a cte to allow us to number the rows. Do you understand the cte I used? Did you understand why I did that?

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/