February 1, 2012 at 6:50 am
Got a request for some information and to have 1 row per order. The request includes the inks used on each order, which there may be 1 to 7 inks. Is there a technique to get multi-values into the same row?
Example (Typical Results):
Order / InkID
------------
1234 / 001
1234 / 107
2133 / 222
3144 / 184
3144 / 187
3144 / 327
3144 / 217
Would like:
1234 / 001 / 007
2133 / 222
3144 / 184 / 187 / 327 / 217
[font="Verdana"]"The Road To Success Is Always Under Construction."[/font]
February 1, 2012 at 8:38 am
This is called a PIVOT. Jeff wrote a couple of great articles about this very topic.
http://www.sqlservercentral.com/articles/T-SQL/63681/[/url]
http://www.sqlservercentral.com/articles/Crosstab/65048/[/url]
_______________________________________________________________
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 1, 2012 at 12:10 pm
Hi Sean,
Thanks for the reply. This isn't a Pivot situation. There is no column/field to determine which column it is to be assigned to. Quick analogy: 2 Tables: (1) Parents and (2) Children. The Parents' table contains a column ParentID, and the Childrens table has 2 columns - ParentID, ChildsName. If the data were something like the following in the Childrens table:
ParentID / Childsname
001 / Tom
001 / Mary
001 / Jeff
002 / Jim
003 / Susan
003 / Beth
The desired results would be:
001 / Tom / Mary / Jeff
002 / Jim
003 / Susan / Beth
Hope this clarifies the data.
[font="Verdana"]"The Road To Success Is Always Under Construction."[/font]
February 1, 2012 at 12:27 pm
Does something like this get you closer?
Notice I created ddl and sample data so we know we are all on the same page.
create table #data
(
ParentID int,
ChildName varchar(25)
)
insert #data
select 1, 'Tom'
union all
select 1, 'Mary'
union all
select 1, 'Jeff'
union all
select 2, 'Jim'
union all
select 3, 'Susan'
union all
select 3, 'Beth'
select ParentID, STUFF
(
(
select ' ' + ChildName
from #data d2
where d1.ParentID = d2.ParentID
Order by d2.ChildName
FOR XML PATH('')
) ,1, 1, ' '
)
from #data d1
group by ParentID
select * from #data
drop table #data
_______________________________________________________________
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 1, 2012 at 1:23 pm
Hi Sean,
Thanks for continuing to help me out. I'm working on it, in between other tasks.
1. It looks like we can't split the results into separate columns, but, are going to concatenate into a string.
2. The finished product to the requester will be an Excel (2007) file. I will:
2a) Copy/paste results into the Excel file.
2b) Run the column thru "Text To Columns" functionality to get the desired end result.
2*) This is a LOT faster than what I did on the earlier results: Copy the Inks (when more than 1 row/order) and Paste Special > Transpose columns into extra columns for the transposed Inks.
2*) And LOT is an understatement - the previous results took about 40 minutes to convert the multiple rows into single rows, this will take less than a minute.
So, your solution will work for me.
Of course, the database I am actually working with isn't as straight forward as what I posted. I worked on the technique as its own query - It worked!:
select SPEC_NO, STUFF
(
( select '|' + ink_desc
from spec_prt_info d2
left JOIN INK_CST_F icf on icf.INK_CODE = d2.INK_CODE
where d2.SPEC_NO = d1.SPEC_NO
for XML path('')
) ,1 ,1, ' '
) as InkList
from spec_prt_info d1
group by spec_no
The next step will be to integrate it into the main query, (the query that produces multiple rows because there may be one or more inks per order) Lines 4&5 will be modified with the STUFF technique:
select ORDER_NO, CUST_IDENT, BLANK_LEN, BLANK_WID, DIM_A, DIM_B, DIM_C, COLOR_DESC, inks.INK_DESC, ops.MACH_DESCR, orders.PLT_NO
from ORDERS
left join SPECS on SPECS.SPEC_NO = ORDERS.SPEC_NO
left join ( select SPEC_NO, INK_DESC from SPEC_PRT_INFO spi left JOIN INK_CST_F icf on icf.INK_CODE = spi.INK_CODE
where INK_TYPE = 'C' ) inks on inks.SPEC_NO = SPECS.SPEC_NO
left join ( select spec_no, operations.MACH_NO, MACH_DESCR from OPERATIONS
left join MACHINES on MACHINES.MACH_NO = OPERATIONS.MACH_NO
where operations.MACH_NO in (240,241,245,286,288,290,291,295,300)) ops on ops.spec_no = SPECS.spec_no
where
orders.CSCODE = '2507'
and ORDER_DATE between '01/01/2011' and '12/31/2011'
and COMPLETION_FLG <> 'X'
and orders.PLT_NO = 1
order by ORDER_NO;
Thanks Again! I appreciate the help.
[font="Verdana"]"The Road To Success Is Always Under Construction."[/font]
February 1, 2012 at 1:26 pm
What you are working is a dynamic cross tab. It is outlined in the second article I posted but you have to get a bit creative with your aggregation to pull off what you are trying to do. This kind of thing is really really really difficult to pull off in sql because it defies the concepts of normalization completely. Glad you found a way to make it work.
_______________________________________________________________
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 1, 2012 at 2:16 pm
Again - Thanks for your help (and Excel's Text To Column Functionality).
[font="Verdana"]"The Road To Success Is Always Under Construction."[/font]
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply