SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Convert Row Data in 1 Field to Many Columns


Convert Row Data in 1 Field to Many Columns

Author
Message
Japster
Japster
Forum Newbie
Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)

Group: General Forum Members
Points: 5 Visits: 17
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
pietlinden
pietlinden
SSCarpal Tunnel
SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)

Group: General Forum Members
Points: 4870 Visits: 13170
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

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
Cody Konior
SSC-Addicted
SSC-Addicted (433 reputation)SSC-Addicted (433 reputation)SSC-Addicted (433 reputation)SSC-Addicted (433 reputation)SSC-Addicted (433 reputation)SSC-Addicted (433 reputation)SSC-Addicted (433 reputation)SSC-Addicted (433 reputation)

Group: General Forum Members
Points: 433 Visits: 1105
Are you looking for pivot/unpivot?
Jonathan AC Roberts
Jonathan AC Roberts
SSC Eights!
SSC Eights! (877 reputation)SSC Eights! (877 reputation)SSC Eights! (877 reputation)SSC Eights! (877 reputation)SSC Eights! (877 reputation)SSC Eights! (877 reputation)SSC Eights! (877 reputation)SSC Eights! (877 reputation)

Group: General Forum Members
Points: 877 Visits: 1905
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
Japster
Forum Newbie
Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)

Group: General Forum Members
Points: 5 Visits: 17
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
J Livingston SQL
SSCertifiable
SSCertifiable (5.5K reputation)SSCertifiable (5.5K reputation)SSCertifiable (5.5K reputation)SSCertifiable (5.5K reputation)SSCertifiable (5.5K reputation)SSCertifiable (5.5K reputation)SSCertifiable (5.5K reputation)SSCertifiable (5.5K reputation)

Group: General Forum Members
Points: 5451 Visits: 35376
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
Sean Lange
One Orange Chip
One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)

Group: General Forum Members
Points: 26026 Visits: 17528
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.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Japster
Japster
Forum Newbie
Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)

Group: General Forum Members
Points: 5 Visits: 17
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
Jonathan AC Roberts
SSC Eights!
SSC Eights! (877 reputation)SSC Eights! (877 reputation)SSC Eights! (877 reputation)SSC Eights! (877 reputation)SSC Eights! (877 reputation)SSC Eights! (877 reputation)SSC Eights! (877 reputation)SSC Eights! (877 reputation)

Group: General Forum Members
Points: 877 Visits: 1905
Is there a maximum number of distinct serial numbers or are there at most 7 like in your example?
Japster
Japster
Forum Newbie
Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)

Group: General Forum Members
Points: 5 Visits: 17
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
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search