Pivot table

Question

Post reply

Pivot table

Motz

Old Hand

Points: 310
More actions
September 1, 2009 at 3:13 am

#210152

How can i pivot table without using Pivot function as i have to pivot the string values. Pivot function works only if we have aggregates
Any idea ,sample code will be really helpful
Thanks in advance
Regards,
Motz

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

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

Dave Ballantyne SSC-Dedicated Points: 33667 More actions · Answer 1

You can still use Min and Max on varchar columns, does that help ?

Clear Sky SQL
My Blog[/url]

Motz Old Hand Points: 310 More actions · Answer 2

Motz

Old Hand

Points: 310

September 1, 2009 at 3:35 am

#1047371

No, its not working, it shows null values

Dave Ballantyne SSC-Dedicated Points: 33667 More actions · Answer 3

Please post DDL and sample data that shows your issue and i'll take a look

Clear Sky SQL
My Blog[/url]

Motz Old Hand Points: 310 More actions · Answer 4

I have attached Create , Insert Query

expected output , thr can be more criteria and values

ID Criteria1 Value1 Criteria2 Value2

1 First Input ABC Second Input XYZ

2 N Input PQR Output KLM

Regards,

Motz

Dave Ballantyne SSC-Dedicated Points: 33667 More actions · Answer 5

Try This

with cteTest(ID,Value,RowN,Coln)

as

(

Select ID,

case when Coln =1 then Criteria else Value end,

dense_rank() over (partition by ID order by Criteria),

Coln

from Test

cross join (Select 1 as Coln union Select 2) as Cart(Coln)

)

,

cteColTest(ID,Value,Coln)

as

(

Select Id,

value,

Coln = row_number() over(partition By id order by Rown , Coln)

from cteTest

)

Select ID,max([1]),max([2]),max([3]),max([4])

from cteColTest

pivot(

max(value)

for coln in([1],[2],[3],[4])

) as pvt

group by id

Clear Sky SQL
My Blog[/url]

Motz Old Hand Points: 310 More actions · Answer 6

Thanks for the query

it works fine when criteria is different for a particluar ID , but doesnt work well if Criteria is same for particluarID

I have attached the inserts script, if u insert that and run the query it will give wrong result for ID 3

I am trying to modify that part, Dave if you know how to achive this please let me know

Regards,

Motz

Dave Ballantyne SSC-Dedicated Points: 33667 More actions · Answer 7

Its because of the duplication of criteria screwing over the dense_rank()

this should sort it

with cteTestWithRow(ID,Criteria,Value,Rown)

as

(

Select Id,

Criteria,Value,row_number() over (Partition by id order by Criteria)

from test

)

,cteTest(ID,Value,RowN,Coln)

as

(

Select ID,

case when Coln =1 then Criteria else Value end,

dense_rank() over (partition by ID order by RowN),

Coln

from cteTestWithRow

cross join (Select 1 as Coln union Select 2) as Cart(Coln)

)

,

cteColTest(ID,Value,Coln)

as

(

Select Id,

value,

Coln = row_number() over(partition By id order by Rown , Coln)

from cteTest

)

Select ID,max([1]),max([2]),max([3]),max([4])

from cteColTest

pivot(

max(value)

for coln in([1],[2],[3],[4])

) as pvt

group by id

Clear Sky SQL
My Blog[/url]

Motz Old Hand Points: 310 More actions · Answer 8

Thanks Dave

Now i am trying to create a view out of that but it throws error as below

Msg 4511, Level 16, State 1, Procedure vtest, Line 3

Create View or Function failed because no column name was specified for column 2.

Regards,

Motz

Dave Ballantyne SSC-Dedicated Points: 33667 More actions · Answer 9

Specify the column names like this

Select ID,max([1]) as col1 ,max([2]) as col2 ,max([3]) as col3,max([4]) as col4

from cteColTest

Clear Sky SQL
My Blog[/url]

Motz Old Hand Points: 310 More actions · Answer 10

I already added that part, was some other issue, did some minor mistake

Thanks a Lot Dave for your Help

Regards,

Motz