modifying table structure

[creedprg]

Hello guys.

I would like to create an cursor from my original table the problem is it has different structure.

my original table look like this.

modelno           defect_type            qty
test1             scratch                10
test2             dent                    5
test3             deform                 20

from this table i'de to like to create a cursor look like this.

defect_type        test1          test2       test3
scratch             10
dent                                5
deform                                          20

the model list become a field. is it possible? your advice is highly appreciated... thanks

 

[sabinUE]

Hi,

;with aCTE
AS
(	select 'test1' modelno,'scratch' defect_type,10  qty union all
   select  'test2','dent', 5 union all
   select 'test3','deform', 20 )


select * 
from aCTE
	pivot(SUM(QTY) FOR modelNo in ([test1],[test2],[test3])) PV
Order by defect_type desc

and the output:
defect_type test1 test2 test3
scratch 10 NULL NULL
dent NULL 5 NULL
deform NULL NULL 20



sabin MCP

 

[philhege]

So you don't have to hard-code the modelno values in the pivot:

declare @v_sql varchar(max)

set @v_sql = ''
select @v_sql = @v_sql + coalesce('['+modelno+'],','')
FROM (select distinct modelno from tests) t
--print @v_sql

set @v_sql = '
;with aCTE
AS
(	select * from tests )


select * 
from aCTE
	pivot(SUM(QTY) FOR modelNo in ('+SUBSTRING(@v_sql,1,len(@v_sql)-1)+')) PV
Order by defect_type '

print @v_sql
exec (@v_sql)

-----------
With business clients like mine, you'd be better off herding cats.

 

[SkipVought]

Never 4 tests? Ever???

Why a TABLE rather than a REPORT?

Not a best and accepted practice!!!

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[Olaf Doschke]

Hello Skip,

so you're saying the Reporting Services could take in the data in creedprg's original format and do the pivoting itself?

Like this?

https://www.youtube.com/watch?v=vgtVJf21EXU

Bye, Olaf.