Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2005 Forums
 Transact-SQL (2005)
 t-sql query help needed

Author  Topic 

keyursoni85
Posting Yak Master

233 Posts

Posted - 2010-08-18 : 01:25:54
Hi,

I have created a query with joins and it gives output as below..
I have category table and its properties will be there.. after each category row..
My query output is as below:
CatID CatName PropertyDesc PropertyValue
1 Category1 -- --
1 Category1 Cat1Prop1 9393
1 Category1 Cat1Prop2 444
1 Category1 Cat1Prop3 3434
2 Category2 -- --
2 Category2 xyzprop 894489
3 Category3 -- --
3 Category3 teste 33333
3 Category4 -- --

Above is output for each category with its property values after category row..
now i need report for property on column basis not row basis..as below..

CatID CatName Cat1Prop1 Cat1Prop2 Cat1Prop3 xyzprop teste
1 Category1 9393 444 3434 -- --
2 Category2 -- -- -- 894489 --
3 Category3 -- -- -- -- 33333
4 Category4 -- -- -- -- --

Rows are bounded on column.. and there are number of properties for each category..
how can i achive this dynamically.. please help or any inputs or ideas on this output..

thanks

pk_bohra
Master Smack Fu Yak Hacker

1182 Posts

Posted - 2010-08-18 : 01:48:23
You need to use dynamic pivot to achieve this.

Have a look at:
http://beyondrelational.com/blogs/madhivanan/archive/2008/08/27/dynamic-pivot-in-sql-server-2005.aspx

Regards,
Bohra

I am here to learn from Masters and help new bees in learning.
Go to Top of Page

keyursoni85
Posting Yak Master

233 Posts

Posted - 2010-08-18 : 04:07:07
Thank you,
But i dont want any group to pass as summaries in pivot function..
Go to Top of Page

manub22
Starting Member

6 Posts

Posted - 2010-08-18 : 04:08:15
Dynamic pivot won't work here, as it requires aggregate function mandatory to use.

Can be resolved simply by adding 4-5 self joins.
This link should help you: http://sqlwithmanoj.com/2010/08/17/convert-multiple-rows-into-a-single-column/
Go to Top of Page

keyursoni85
Posting Yak Master

233 Posts

Posted - 2010-08-18 : 04:55:38
quote:
Originally posted by manub22

Dynamic pivot won't work here, as it requires aggregate function mandatory to use.

Can be resolved simply by adding 4-5 self joins.
This link should help you: http://sqlwithmanoj.blogspot.com/2010/08/convert-multiple-rows-into-single.html



This is fine but it will not help because its static column name given to display.. i have dynamic columns coming for category's properties..
Go to Top of Page

keyursoni85
Posting Yak Master

233 Posts

Posted - 2010-08-18 : 05:27:25
Any solution for above query..
i tried with pivot but i dont want to group my rows.. property desc as column name and its value as property desc columns's value..
Go to Top of Page

keyursoni85
Posting Yak Master

233 Posts

Posted - 2010-08-18 : 07:13:53
My table/query 's output is like..

CREATE TABLE #tmptable (catid int, catname varchar(50), propdesc varchar(50), propvalue varchar(50))
insert into #tmptable values (1, 'cat1', '', '')
insert into #tmptable values (1, 'cat1', 'cpu', '442')
insert into #tmptable values (1, 'cat1', 'pus', 'testlsekjr')
insert into #tmptable values (1, 'cat1', 'interface', 'ie-s33')
insert into #tmptable values (2, 'cat2', '', '')
insert into #tmptable values (2, 'cat2', 'cpu', '663')
insert into #tmptable values (2, 'cat2', 'blcoksc', '88jjauwe')
insert into #tmptable values (3, 'cat3', '', '')
insert into #tmptable values (4, 'cat4', '', '')
insert into #tmptable values (4, 'cat4', 'abcdef', 'xyz value')
insert into #tmptable values (4, 'cat4', 'prop desc', 'value lse value')

And i used below query but it gives multiple rows for

select * from
(
select propdesc as pivot_col, * from crosstabtbl --GROUP BY catid, propvalue, propdesc
) as t
pivot
(
MAX(propvalue) for pivot_col in ([abcdef],[blcoksc],[cpu],[interface],[prop desc],[pus])
) as p


but it gives multiple rows...i want property value in first category row..
thank you
Go to Top of Page

pk_bohra
Master Smack Fu Yak Hacker

1182 Posts

Posted - 2010-08-18 : 07:29:28
Try this:

--============================================================
--Sample table and data
--============================================================

create table Category
(
Srno Int identity,
CatID int,
CatName Varchar(255),
PropertyDesc Varchar(255),
PropertyValue Varchar(255)
)

Insert into Category
Select 1, 'Category1', '--', '--' union
Select 1, 'Category1', 'Cat1Prop1','9393' union
Select 1, 'Category1', 'Cat1Prop2','444' union
Select 1, 'Category1', 'Cat1Prop3','3434' union
Select 2, 'Category2', '--','--' union
Select 2, 'Category2', 'xyzprop','894489' union
Select 3, 'Category3', '--','--' union
Select 3, 'Category3', 'teste','33333' union
Select 4, 'Category4', '--','--'



--====================================================================
--Solution from here

Declare @sqlStmt varchar(max) --Variable for capturing column names
Declare @PvtStmt varchar(max) --Variable for preparing dynamice pivot stmt

--Capture all the columns to be pivoted
Select @sqlStmt = isnull(@sqlStmt + ' ','') + quotename(PropertyDesc) +',' from
(select distinct PropertyDesc from Category where PropertyDesc <> '--' ) as SubTab

set @sqlStmt = Left(@sqlStmt,len(@sqlStmt) -1)

--Prepare a pivot statement to be executed dynamically
Set @PvtStmt = 'Select CatID, CatName , ' + @sqlStmt +
'
From
(select CatID, CatName , PropertyDesc,PropertyValue from Category ) P
Pivot
(Max(PropertyValue) for PropertyDesc
in ( ' + @sqlStmt + '
)) as Pvt
Order by CatID'

exec (@PvtStmt)

--Solution ends here
--====================================================================


Regards,
Bohra



I am here to learn from Masters and help new bees in learning.
Go to Top of Page

keyursoni85
Posting Yak Master

233 Posts

Posted - 2010-08-18 : 09:28:18
thank you bohra : )
Go to Top of Page

pk_bohra
Master Smack Fu Yak Hacker

1182 Posts

Posted - 2010-08-18 : 11:48:54
quote:
Originally posted by keyursoni85

thank you bohra : )



You are welcome
Go to Top of Page
   

- Advertisement -