Help with a PIVOT

Question

Help with a PIVOT

pnr8uk

SSCommitted

Points: 1828
More actions
August 3, 2016 at 7:47 am

#328910

Hi All
I am having a bit of nightmare with a pivot query. When I say nightmare... I am nearly there but it is driving me crazy.
To reproduce - create the following table. Using the attached script.
Then I query PIVOT on the first 3 rows - these are the only rows I am interested in:
Using this query: The first query before the cte is just for reference.
select top 3
id,Exsubcat,Sales1,Sales2,Sales3,Sales4,Sales5
from BusinessExpense
;
with bah (id,ExSubCat,Sales1,Sales2,Sales3,Sales4,Sales5,RowId)
as
(
select Id,ExSubCat,Sales1,Sales2,Sales3,Sales4,Sales5, ROW_NUMBER() over (partition by(Sales1) order by id)
as RowId from BusinessExpense
)
select *
from
(select Sales1,ExSubCat,RowId
from bah) c
pivot
(
max(Sales1)
for ExSubCat IN (
[ENTER TCO NAME]
,[ENTER ACC NUMBER]
,[ENTER ACC NAME]
)
) as pivottable;
The first row is fine in the return, however I can't get Sales2, Sales3, Sales4, Sales5
Any help greatly received.

Viewing 14 posts - 1 through 13 (of 13 total)

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

drew.allen SSC Guru Points: 76985 More actions · Answer 1

You haven't supplied the expected output, but I suspect you are trying to transpose the data rather than pivot the data. You would need to unpivot followed by a pivot.

Drew

J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA

pnr8uk SSCommitted Points: 1828 More actions · Answer 2

Hi Drew

I thought I had included the expected output as an image.

You are correct it's wrong of me to call this a PIVOT in the truest term, it is a transpose.

Thanks anyway.

Luis Cazares SSC Guru Points: 183706 More actions · Answer 3

Is this what you need? Note that instead of pivot I'm using cross tabs and instead of unpivot I'm using CROSS APPLY.

SELECT RowId,

MAX( CASE WHEN ExSubCat = 'ENTER TCO NAME' THEN Sales END) AS [ENTER TCO NAME],

MAX( CASE WHEN ExSubCat = 'ENTER ACC NUMBER' THEN Sales END) AS [ENTER ACC NUMBER],

MAX( CASE WHEN ExSubCat = 'ENTER ACC NAME' THEN Sales END) AS [ENTER ACC NAME]

FROM BusinessExpense

CROSS APPLY (VALUES( 1, Sales1),

( 2, Sales2),

( 3, Sales3),

( 4, Sales4),

( 5, Sales5))u(RowId, Sales)

GROUP BY RowId;

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

pnr8uk SSCommitted Points: 1828 More actions · Answer 4

Thank you Louis - I haven't used CROSS TAB before, makes life a lot easier than trying to use something not designed for transposing.

This is all I need I think.

Thank you again

P

Luis Cazares SSC Guru Points: 183706 More actions · Answer 5

pnr8uk (8/3/2016)
Thank you Louis - I haven't used CROSS TAB before, makes life a lot easier than trying to use something not designed for transposing.
This is all I need I think.
Thank you again
P

Great! Now, the big question: Do you understand it?

If you don't you should ask more questions.

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

pnr8uk SSCommitted Points: 1828 More actions · Answer 6

I understand - it works for what I want.

I will now educate myself on the query itself, take it apart etc, put it in my armoury

Thanks you sir

Luis Cazares SSC Guru Points: 183706 More actions · Answer 7

pnr8uk (8/3/2016)
What does the u in this line mean?
( 5, Sales5))u(RowId, Sales)
Thanks again
Paul

The u is a table alias (even if it's not a table, but a Table Value Constructor). I used "u" for Unpivot.

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

drew.allen SSC Guru Points: 76985 More actions · Answer 8

Luis Cazares (8/3/2016)
pnr8uk (8/3/2016)
What does the u in this line mean?
( 5, Sales5))u(RowId, Sales)
Thanks again
Paul
The u is a table alias (even if it's not a table, but a Table Value Constructor). I used "u" for Unpivot.

Isn't it? I mean, why do you think they call it a TABLE Value Constructor? It's a table constructed from a set of values.

Drew

J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA

Luis Cazares SSC Guru Points: 183706 More actions · Answer 9

drew.allen (8/3/2016)
Luis Cazares (8/3/2016)
pnr8uk (8/3/2016)
What does the u in this line mean?
( 5, Sales5))u(RowId, Sales)
Thanks again
Paul
The u is a table alias (even if it's not a table, but a Table Value Constructor). I used "u" for Unpivot.
Isn't it? I mean, why do you think they call it a TABLE Value Constructor? It's a table constructed from a set of values.
Drew

It acts like a table, but it's just an expression. It's the same as table-valued functions and common table expressions. None of them are tables or store data in them, they just act as tables.

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

pietlinden SSC Guru Points: 63503 More actions · Answer 10

pietlinden

SSC Guru

Points: 63503

August 3, 2016 at 2:38 pm

#1893618

Read Kenneth Fisher's blog[/url] about it.

drew.allen SSC Guru Points: 76985 More actions · Answer 11

Luis Cazares (8/3/2016)
drew.allen (8/3/2016)
Luis Cazares (8/3/2016)
pnr8uk (8/3/2016)
What does the u in this line mean?
( 5, Sales5))u(RowId, Sales)
Thanks again
Paul
The u is a table alias (even if it's not a table, but a Table Value Constructor). I used "u" for Unpivot.
Isn't it? I mean, why do you think they call it a TABLE Value Constructor? It's a table constructed from a set of values.
Drew
It acts like a table, but it's just an expression. It's the same as table-valued functions and common table expressions. None of them are tables or store data in them, they just act as tables.

Maybe it acts like a table, because it is a table. It has rows, it has columns. Isn't that enough to make it a table? Views are included in INFORMATION_SCHEMA.TABLES, because they are tables in this general sense. You can't store data in the results of a join, but that's still a table--albeit a virtual one.

I also have no problem with TABLE having two different definitions, in the same way that I have no problem with CAT having two different definitions, because one is a specific instance of a more general group in both cases.

Drew

J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA

MMartin1 One Orange Chip Points: 27795 More actions · Answer 12

drew.allen (8/3/2016)
Luis Cazares (8/3/2016)
drew.allen (8/3/2016)
Luis Cazares (8/3/2016)
pnr8uk (8/3/2016)
What does the u in this line mean?
( 5, Sales5))u(RowId, Sales)
Thanks again
Paul
The u is a table alias (even if it's not a table, but a Table Value Constructor). I used "u" for Unpivot.
Isn't it? I mean, why do you think they call it a TABLE Value Constructor? It's a table constructed from a set of values.
Drew
It acts like a table, but it's just an expression. It's the same as table-valued functions and common table expressions. None of them are tables or store data in them, they just act as tables.
Maybe it acts like a table, because it is a table. It has rows, it has columns. Isn't that enough to make it a table? Views are included in INFORMATION_SCHEMA.TABLES, because they are tables in this general sense. You can't store data in the results of a join, but that's still a table--albeit a virtual one.
I also have no problem with TABLE having two different definitions, in the same way that I have no problem with CAT having two different definitions, because one is a specific instance of a more general group in both cases.
Drew

I distinguish a 'table' from a record set. I let Sql Server define a table as an object in sys.tables. Though these and CTEs, joins, etc... manifest themselves as record sets in the internals of Sql Server. Record set are tabular of course. It depends on perspective.

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

The Dixie Flatline SSC Guru Points: 53354 More actions · Answer 13

VALUES is not a table. It is a table value constructor, because the values clause can contain expressions which are used to construct the actual values produced. Tables can have values in columns, even computed values in computed columns. But you can't cross join to a table and do this:

declare @input table(Amount int)

insert into @input

select 36 union all

select 16

select * from @input

-- the value in the Result column is constructed

select Amount, v.Result

from @input

cross apply (values (Amount),(Amount + 1), (Amount * 10), (Amount+100), (Sqrt(Amount)), (Amount/2)) v (Result)

__________________________________________________

Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills