Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Convert Row Data in 1 Field to Many Columns Expand / Collapse
Author
Message
Posted Wednesday, April 2, 2014 3:09 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, April 9, 2014 9:21 AM
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
Post #1557366
Posted Sunday, April 6, 2014 11:02 PM
Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: Today @ 6:45 AM
Points: 773, Visits: 4,972
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.
Post #1558908
Posted Sunday, April 6, 2014 11:11 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: 2 days ago @ 6:25 AM
Points: 216, Visits: 851
Are you looking for pivot/unpivot?
Post #1558912
Posted Monday, April 7, 2014 10:00 AM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Friday, September 12, 2014 3:38 AM
Points: 338, Visits: 1,426
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

Post #1559169
Posted Tuesday, April 8, 2014 2:28 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, April 9, 2014 9:21 AM
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
Post #1559400
Posted Tuesday, April 8, 2014 11:36 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Today @ 9:02 AM
Points: 1,920, Visits: 19,342
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
Post #1559610
Posted Tuesday, April 8, 2014 12:50 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 9:03 AM
Points: 13,446, Visits: 12,308
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 Moden's 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)
Post #1559661
Posted Wednesday, April 9, 2014 6:34 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, April 9, 2014 9:21 AM
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
Post #1559907
Posted Wednesday, April 9, 2014 8:05 AM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Friday, September 12, 2014 3:38 AM
Points: 338, Visits: 1,426
Is there a maximum number of distinct serial numbers or are there at most 7 like in your example?
Post #1559963
Posted Wednesday, April 9, 2014 8:53 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, April 9, 2014 9:21 AM
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
Post #1560004
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse