February 20, 2014 at 8:58 am
Hello I have a problem, well 2 problems.
I have a table that looks like this.
Product ID| Sequence #| Code
12345 | 01 | 5645
12345 | 02 | 1234
There can be up to 26 sequence #'s
I need to create a table that looks like this
Product id| Sequence 01| sequence 02|...........
12345 | 5645 | 1234 |
How can I convert my vertical table to a horizontal table?
February 20, 2014 at 9:00 am
montecarlo2079 (2/20/2014)
Hello I have a problem, well 2 problems.I have a table that looks like this.
Product ID| Sequence #| Code
12345 | 01 | 5645
12345 | 02 | 1234
There can be up to 26 sequence #'s
I need to create a table that looks like this
Product id| Sequence 01| sequence 02|...........
12345 | 5645 | 1234 |
How can I convert my vertical table to a horizontal table?
Take a look at the two articles in my signature about cross tabs. The first one explains the concepts and how to do it when you have a known amount of values. The second one is when the amount of values is unknown.
If you still need help you might take a look at the first article in my signature for best practices when posting questions.
_______________________________________________________________
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/
February 20, 2014 at 9:10 am
That article gave me a start but I dont need to sum any data,
I just need to display it in the fashion above. no calculations
maybe i didnt read the article correctly but I didnt see a way that explains how to do what i need it to do?
February 20, 2014 at 9:17 am
montecarlo2079 (2/20/2014)
That article gave me a start but I dont need to sum any data,I just need to display it in the fashion above. no calculations
maybe i didnt read the article correctly but I didnt see a way that explains how to do what i need it to do?
You can still use aggregate data with MAX.
I am going to guess you didn't read the article I suggested about best practices.
You should post consumable ddl and sample data. I created it for you this time. Then I demonstrate how you can use a cross tab for this.
if OBJECT_ID('tempdb..#Something') is not null
drop table #Something
create table #Something
(
ProductID int,
SeqNum int,
Code int
)
insert #Something
select 12345, 01, 5645 union all
select 12345, 02, 1234
select ProductID
, MAX(case when SeqNum = 1 then Code end) as Seq1
, MAX(case when SeqNum = 2 then Code end) as Seq2
from #Something
group by ProductID
_______________________________________________________________
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/
February 21, 2014 at 7:35 am
Thanks Sean, I was trying to figure out something similar the other day and couldn't think of how to get it down to one row per ID. Without doing an update statement. This is so simple, but brilliant, but for the life of me I could think of it. :crazy: Just goes to show you don't know as much as you think you do.
-------------------------------------------------------------
we travel not to escape life but for life not to escape us
Don't fear failure, fear regret.
February 21, 2014 at 7:51 am
below86 (2/21/2014)
Thanks Sean, I was trying to figure out something similar the other day and couldn't think of how to get it down to one row per ID. Without doing an update statement. This is so simple, but brilliant, but for the life of me I could think of it. :crazy: Just goes to show you don't know as much as you think you do.
Glad you found that helpful.
_______________________________________________________________
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/
February 21, 2014 at 9:09 am
Yep thanks sean,
I used the max for all of the columns...did exactly what I needed it to.
but one question, do I need to create a temp table to make it work or can I do it off an existing table?
February 21, 2014 at 9:21 am
montecarlo2079 (2/21/2014)
Yep thanks sean,I used the max for all of the columns...did exactly what I needed it to.
but one question, do I need to create a temp table to make it work or can I do it off an existing table?
Your existing table is fine. I had to create a temp table on my system to demonstrate how it works because I don't have whatever your table is.
_______________________________________________________________
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 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply