April 2, 2014 at 3:09 am
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
April 6, 2014 at 11:02 pm
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.
April 6, 2014 at 11:11 pm
Are you looking for pivot/unpivot?
April 7, 2014 at 10:00 am
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
April 8, 2014 at 2:28 am
Thanks for the information,
1st day back in work today so will take a look and let you know how I get on
April 8, 2014 at 11:36 am
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
April 8, 2014 at 12:50 pm
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/
April 9, 2014 at 6:34 am
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
April 9, 2014 at 8:05 am
Is there a maximum number of distinct serial numbers or are there at most 7 like in your example?
April 9, 2014 at 8:53 am
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
April 9, 2014 at 8:58 am
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/
April 9, 2014 at 9:17 am
So this is your homework?
April 9, 2014 at 9:25 am
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:
April 9, 2014 at 9:33 am
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/
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply