Looks like a PIVOT table problem, but for text, not aggregate data

Question

Looks like a PIVOT table problem, but for text, not aggregate data

Rhett Lowson

Mr or Mrs. 500

Points: 552
More actions
March 16, 2010 at 9:13 am

#218005

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.

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Hunterwood SSCrazy Points: 2074 More actions · Answer 1

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

Paul White SSC Guru Points: 150467 More actions · Answer 2

For another option, see Jeff Moden's articles on pivots and cross-tabs:

Part One[/url]

Part Two[/url] (covers dynamic stuff)

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

Jeff Moden SSC Guru Points: 1003873 More actions · Answer 3

Rhett Lowson (3/16/2010)
but I can't 'aggregate' my text

Sure you can... MAX...

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)