Using PIVOT without aggregates

Question

Using PIVOT without aggregates

ducon

Hall of Fame

Points: 3801
More actions
June 6, 2007 at 8:58 am

#196269

Hello,
I have a table with 3 columns: Label, Value, Language (all are varchar). I want to fetch all rows for a given Language, however I want to have the results this way: Label would be the column header, and Value the the actual value. That is, instead of having 50 rows with 2 columns, I want 1 row with 50 columns. Is there a way to achieve this? If so how? I've been banging my head on PIVOT, which I think is the way to go, but I am not doing any aggregates in my query, I really just want some data "flipped over". According to BOL, the aggregation is "possible" not mandatory, but I always get T-SQL syntax errors when I try to use it .
Any ideas how to achieve that? Without a complicated stored procedure to create if possible, I can sort of imagine how to do that but I would like to use something like PIVOT if that's possible.
Thanks a lot.
Greg

Viewing 11 posts - 1 through 10 (of 10 total)

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

Anders Pedersen SSChampion Points: 11410 More actions · Answer 1

I think the documentation is wrong on this point! At least I have not found a way to do this without aggregation of some sort.

Jason Selburg SSC-Insane Points: 24641 More actions · Answer 2

Can you post the DDL for your table and some sample data along with required results? Your question is fairly straight forward, but without sample data it's hard to exactly know what results you want and from what...

Thanks! 🙂

______________________________________________________________________

Personal Motto: Why push the envelope when you can just open it?

If you follow the direction given HERE[/url] you'll likely increase the number and quality of responses you get to your question.

Jason L. Selburg

ducon Hall of Fame Points: 3801 More actions · Answer 3

Sure thing, and thanks for the help . Basically, this summarizes what I want (it's not exactly it but I'm not at work right now and if you can make this work I'll be able to take it from there):

CREATE TABLE test(label_value VARCHAR(25) NOT NULL PRIMARY KEY, display_value VARCHAR(25) NOT NULL, lan CHAR(2) NOT NULL);

INSERT INTO test(label_value, display_value, lan) VALUES ('label.test', 'Test', 'en');

INSERT INTO test(label_value, display_value, lan) VALUES ('label.test2', 'Test 2', 'en');

If I do SELECT label_value, display_value FROM test WHERE lan='en' I get this:

label_value display_value

-----------------------------

label.test Test

label.test2 Test 2

But I would like it this way:

label.test label.test2

-----------------------------

Test Test2

I actually managed to get it working using a cursor and an in-memory table o that I can get it out this way. But I wanted to know if this was doable using a PIVOT operator.

Many thanks for the help,

Greg

Jason Selburg SSC-Insane Points: 24641 More actions · Answer 4

If ALL of your label_value's are known then yes, you can. If not, then it can not be done with PIVOT. That's the one downfall with PIVOT that I've come upon.

So the question to you is, are all of the label_value's known and not variable. More specifically, will they ever change?

______________________________________________________________________

Personal Motto: Why push the envelope when you can just open it?

If you follow the direction given HERE[/url] you'll likely increase the number and quality of responses you get to your question.

Jason L. Selburg

ducon Hall of Fame Points: 3801 More actions · Answer 5

Hi,

No they will not change this is a table that will contain static data based on whatever we want it to contain. But this will be a definite number of records that we know of.

Thanks,

Greg

Anders Pedersen SSChampion Points: 11410 More actions · Answer 6

Actually you can do dynamic names for columns with pivot function, just have to use dynamic SQL (and it becomes a rather "fun" query to read). See below for an example, it's the part after the "for timestamp in" that handles the dynamic column names.

set

quoted_identifier off

declare

@sql varchar(1000);

set

@sql = 'select *

from (select substring(name,1,(dbo.fcn_CharindexFiles(name))) as DBName, sum([Size (MB)]) [Size (MB)] , convert(varchar(10),timestamp,101) as timestamp

from dbo.GrowthDetails

group by substring(name,1,(dbo.fcn_CharindexFiles(name))), convert(varchar(10),timestamp,101)

) as B

pivot(

sum([Size (MB)])

for timestamp in('

+

stuff

(

select N',' + quotename(convert(varchar(10),timestamp,101)) as [text()]

from (select distinct top (@x) timestamp from GrowthDetails order by timestamp desc) as D

order by timestamp

for XML Path('')), 1, 1, N'')+

')) as P;'

;

--select @sql

exec

(@sql)

ducon Hall of Fame Points: 3801 More actions · Answer 7

Hello,

Thanks for the reply. I managed to get the dynamic columns working using the stored procedure with a cursor as I have mentioned before, but doing the pivot myself basically. I could always post the code here, however this is not a valid solution for me.

Here's the evil scheme behind this... I wanted to use this in an SSRS report. Problem is... Such a dataset would not return named columns, and report items have to be bound to columns that are explicitely returned in a query (as far as I've seen, please correct me if I'm wrong but dynamic SQL does not seem to be usable in this case). Hence, I get errors when I try to run my report with such dynamic name.

Even if I know for sure 'label_test' will be returned and I set it in the expression of my report item, I get an error at run-time because it cannot bind the field onto the dataset. This was all started because I was trying to achieve this: http://www.sqlservercentral.com/forums/shwmessage.aspx?forumid=150&messageid=370421 Anyways originally I wanted to achieve this but afterwards I was thinking of flipping the results so that each label_value is a column header and I can have only 1 row, hence no need to dynamically "browse" my dataset. I just need to set the proper column header.

I got the original purpose working using Custom Code and a dirty trick that I'm not too proud of, but I found that's the only way to have it working. But now I'm interested in PIVOT overall to satisfy my curiosity

Greg

Jason Selburg SSC-Insane Points: 24641 More actions · Answer 8

Sorry, Anders is correct. You can use dynamic sql, but I try to avoid it...

______________________________________________________________________

Personal Motto: Why push the envelope when you can just open it?

If you follow the direction given HERE[/url] you'll likely increase the number and quality of responses you get to your question.

Jason L. Selburg

Anders Pedersen SSChampion Points: 11410 More actions · Answer 9

So do I, but using pivot it seems almost necessary to do it. I don't know about the rest of you, but rarely do I know excatly what the data will be that I am making into columns. Usualy because I am working with dates for reports, I guess if you are doing reports on products etc you would know, but even then I would think making it dynamic once would be beneficial so you don't have to change it every time you add a new product.

ducon Hall of Fame Points: 3801 More actions · Answer 10

That is exactly my point. I hardly ever know what column headers I explicitely want. I know I want a certain value to become the column header, but how many there are etc... is hardly known unless you're working using a well-defined dimension, which makes sense in data warehouse environments. But in "traditional" environments, I'm not sure PIVOT fits the bill very well. It's neat, but not that great when you try to use it with SSRS for example and you don't know your columns.

Greg