Simple SQL Pivot

[Brianfree]

HI, how can i create simple sql pivot to do this..

[current output]
NAME, VALUE
JOHN, 10
JOHN, 12
JOHN, 15
PAUL, 8
PAUL, 10
SARAH, 1
SARAH, 2
SARAH, 3
SARAH, 4
SARAH, 5
etc...

[new output]
NAME, VALUE1, VALUE2
JOHN, 10, 12, 14
PAUL, 8, 10
SARAH, 1, 2, 3, 4, 5
etc...

Many thanks Brian

 

[Andrzejek]

Please, format your post(s) appropriately

This does not make sense, is that what you want:
[new output]
[pre]
NAME VALUE1 VALUE2 [red]??? ??? ???[/red]
JOHN 10 12 14 [red]??? ???[/red]
PAUL 8 10 [red]??? ??? ???[/red]
SARAH 1 2 3 4 5
etc...[/pre]

or

[pre]
[new output]
NAME VALUE1
JOHN 10, 12, 14
PAUL 8, 10
SARAH 1, 2, 3, 4, 5
etc...
[/pre]

---- Andy

"Hmm...they have the internet on computers now"--Homer Simpson

 

[Brianfree]

Sorry, i meant..

NAME VALUE1 VALUE2 VALUE3 VALUE4 VALUE5
JOHN 10 12 14 NULL NULL
PAUL 8 10 NULL NULL NULL
SARAH 1 2 3 4 5

The Name columne could be hundreds od rows and the value column could be up to 20

i hope this helps

thankyou
BF

 

[Andrzejek]

Please, format your post(s) appropriately
Use Preview before posting.

---- Andy

"Hmm...they have the internet on computers now"--Homer Simpson

 

[Brianfree]

Hi Sorry, i cant find a way to format this, is this what you mean?

NAME, VALUE1, VALUE2, VALUE3, VALUE4, VALUE5
JOHN, 10, 12, 14, NULL, NULL
PAUL, 8, 10, NULL, NULL, NULL
SARAH, 1, 2, 3, 4, 5

Many thanks

BF

 

[Andrzejek]

Does it look right when you Preview your post?
Since 2008 you probably should figure it out by now how to format your posts...

Hint:

---- Andy

"Hmm...they have the internet on computers now"--Homer Simpson

 

[Chris Miller]

Andrzejek,

calm down, friend.
Nobody's life is at risk here.
Are you having a bad day? You won't make it better by handing it to the next one.
Also, sorry for being so narrow-minded in the previous question.

We all have our ups and downs.

It's already clear without the precise formatting you demand.
Not one column with CSV, many columns with each one value, filled up with NULLS.
It wouldn't even make sense to name a column with the CSV VALUE1, it would be named VALUES and there would be no column numbering.

The one thing is not usual about this, is that you don't aggregate anything. Or differently said: You just want to list the values. Therefore you'll need to create groups of size 1 and then can get the only value of each group with MIN(), MAX(), or AVG() as all of these are the same for a single value. Puzzled? It becomes a bit clearer when I post the PIVOT statement:

Declare @data as table ([NAME] char(10), [VALUE] integer)
Insert into @data values 
('JOHN', 10),
('JOHN', 12),
('JOHN', 15),
('PAUL', 8),
('PAUL', 10),
('SARAH', 1),
('SARAH', 2),
('SARAH', 3),
('SARAH', 4),
('SARAH', 5)

;WITH numberedvalues as
(SELECT [Name], 
ROW_NUMBER() OVER (PARTITION BY [Name] ORDER BY [Name]) AS Rownum, 
[VALUE]
FROM @data
)
SELECT [Name], [1] as [VALUE1], [2] as [VALUE2], [3] as [VALUE3], [4] as [VALUE4], [5] as [VALUE5], [6] as [VALUE6]
FROM numberedvalues 
PIVOT (Max([Value]) FOR Rownum in ([1],[2],[3],[4],[5],[6]) ) as p

The Row_Number creates the columns, without the renaming in the last part they would come out as _1, _2, _3, etc.
Also since each value connected to each person's name has its row_number the groups of (name,row_number,value) each have size 1, only 1 value. A usual pivoting would for example SUM all sales of the same year.

The actual Pivot statement Needs to aggregate each group into one value that's assigned to one of the pivot columns. [Value] here would be the same as Max([Value)), but you'd get an error for not using an aggregation function.

In short, that also means you're not really pivoting data here, not by what it's meant to do. Yes, you're literally making rows to columns, that's the natural language meaning of pivoting something. But that's not the only job of PIVOT.

Your goal could also be done by a lot of CASE, though it will also not look easier, then.

And if you want the pivot because that's how you want to display the data or print a report, there are ways to design a report to print a row for each name and put the rows for the same name into columns while you print/create the report output. There is no need to format data, that's not the job of SQL, really, that's part of frontend code or report engines. Always consider this, as the SQL Server is serving many clients, if clients can take data as is and format it, you make better use of the distributed CPU power at your hands. That the data is somewhat condensed in comparison with all the rows doesn't matter here, you're needing quite a bit of processing to get there, and that's shared by all clients.

Okay, and as the last thing, I should also not sweep under the rug that this does not automatically expand to further Value columns. You can manually extend it to 20, which is sufficient by your specs, but you could of course also go one level further and COUNT(*) how many values you have GOUPed BY [Name], so you know the max [ValueN] column needed. This can be put into an @sql string executed by sp_executesql.

Chriss

 

[Chris Miller]

A screenshot to show it works as is:

If you have problems getting this to work with your data. The only thing you need to adapt, if your columns are named the same is replace @data with our tablename. And remove the part that I just used to have the sample data you gave us for playing with PIVOT.

Chriss

 

[Andrzejek]

Chriss,
I should of sprinkled a few here and there to show I have no bad intentions.
No 'bad day' just a regular (whatever it is today)

---- Andy

"Hmm...they have the internet on computers now"--Homer Simpson

 

[Chris Miller]

Brian, I see you last time visited tek-tips on November 1st. I wonder if it was because you got a notice of the post or it was before.
There's no obligation to react timely to your own threads, I also don't need your confirmation as I tested my idea. I still see a degradation of forum activity due to several reasons, among which missing reactions is one part.

Let me just add how little effort it is to pivot data, just turn coumns to rows and vice versa, with a simple loop detecting where a new ros starts in data not pivoted with some pseudo code starting with the core siple non-pivot query that also happens first in my pivot solution:

SELECT [Name], 
ROW_NUMBER() OVER (PARTITION BY [Name] ORDER BY [Name]) AS Colnum, 
[VALUE]
FROM @data

Notice the name change of Rownum to Colnum.

This will result in
[pre]
Name ColNum Value
JOHN 1 10
JOHN 2 12
JOHN 3 15
PAUL 1 8
PAUL 2 10
SARAH 1 1
SARAH 2 2
SARAH 3 3
SARAH 4 4
SARAH 5 5[/pre]

Now it is ver easy to iterate this data and put it into table cells (or whatever display or report mecchanism):

int RowNum=0;
foreach(record) {
   if (record.ColNum==1) Rownum++;
   display.tablecell(RowNum,record.ColNum).Value = record.Value;
}

In this pseudocode example display.tablecell could be an Excel worksheet with its Cells colelction, for example. I would even say it's just due to lack of undderstanding about concepts that you take for granted a seet of data has to be just as you wwantt to display it. Data does not need to be formatted 1:1 you want to display it, this can be done by mechanisms of the frontend much simpler than with Pivot, don't you also agree? Even with dynamic SQL this simple loop is much easier to get and maintain than the pivoting logic T-SQL needs. And sure, that's also due to how complicated PIVOT is, but its main aim isn't just pivoting, but at the same time aggregating data.

Insted of using that complex too, the rows of data can contain a column number which in the end determines where it is positioned in the visualization. I'd even say it's veryy natural, just think of diagrams you do from data, with an axis in time (years, quarters or x coordinates in the most general form, which are always coming in rows of data, also if you do diagrams of raw data in spreadsheets. Just like a time axis is by mathematical convention to the right, like the x axis of a coordinate system, while time actually isn't a spatial direction. It's already not hard to think along these lines (ar axes). You can apply this thought and even go one step further and just put the data rows 1:1 into a spreadsheet without pivoting it and let that step be done when you assign this data rows to a diagram that has ColNum for the ais that goes to the right. It's really only and purely a presentation layer task to do that.

So more often than not, maybe don't pivot data but let the frontend do that, it is a presentation task, not a data aggregation or transformation task.

Chriss