March 16, 2010 at 9:13 am
I have a table that describes a matrix of data as individual records, and I need to reassemble that data back into a matrix I can have users view in an Excel spreadsheet.
The data would look something like this:
product_code effect effected_item effect_notes
------------ ------ ------------- ------------
1 1 1036 NULL
1 2 994 'edible'
2 1 667 'yellow variety only'
3 2 8883 'seedlings'
And I would want to get this into a sheet with column headings as follows
effected_item | effect | product1_notes | product2_notes | etc.
At first it looked like a case for PIVOT tables, but I found out that it is intended for aggregated data, but I can't 'aggregate' my text. The data itself only allows for one effect (+ notes) for each product, so I am sure the matrix will work out properly if I can just figure out how to write the query.
March 17, 2010 at 3:04 am
Hi Rhett
Since you didn't provide a very clear description of what you want I can only answer in general and show an example, to get a more detailed answer, please read the article http://www.sqlservercentral.com/articles/Best+Practices/61537/ and post create table statements, sample data and a description of expected result on that sample data...
Now to the question: It is not possible to write a query that returns a dynamic number of columns without building the query dynamically. But since you want to take the result into Excel, perhaps you are satisfied to get a semicolon separated result?
To do that you could do something like this:
-- Table declaration
declare @t table (
product int,
info int,
notes varchar(100)
)
-- Example data
insert into @t
select 1, 1, NULL union all
select 1, 2, 'edible' union all
select 2, 1, 'yellow variety only' union all
select 2, 3, 'green variety only' union all
select 2, 4, 'blue variety only' union all
select 3, 2, 'seedlings'
select cast(T.product as varchar(10)) + myData.info as [Table]
from
(select distinct product from @t) T
cross apply
(select ';' +
cast(info as varchar(10)) +
';' +
ISNULL(notes, '')
from @t X
where X.product = T.product
FOR XML PATH('')
) myData (info)
Would this solve your problem?
/Markus
March 17, 2010 at 4:35 am
For another option, see Jeff Moden's articles on pivots and cross-tabs:
March 17, 2010 at 9:29 am
Rhett Lowson (3/16/2010)
but I can't 'aggregate' my text
Sure you can... MAX...
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply