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.
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 PropertyValue1 Category1 -- --1 Category1 Cat1Prop1 93931 Category1 Cat1Prop2 4441 Category1 Cat1Prop3 34342 Category2 -- --2 Category2 xyzprop 8944893 Category3 -- --3 Category3 teste 333333 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 -- -- -- -- 333334 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 |
|
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.. |
|
|
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/ |
|
|
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.. |
|
|
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.. |
|
|
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 pbut it gives multiple rows...i want property value in first category row..thank you |
|
|
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 CategorySelect 1, 'Category1', '--', '--' unionSelect 1, 'Category1', 'Cat1Prop1','9393' unionSelect 1, 'Category1', 'Cat1Prop2','444' unionSelect 1, 'Category1', 'Cat1Prop3','3434' unionSelect 2, 'Category2', '--','--' unionSelect 2, 'Category2', 'xyzprop','894489' unionSelect 3, 'Category3', '--','--' unionSelect 3, 'Category3', 'teste','33333' unionSelect 4, 'Category4', '--','--' --====================================================================--Solution from hereDeclare @sqlStmt varchar(max) --Variable for capturing column namesDeclare @PvtStmt varchar(max) --Variable for preparing dynamice pivot stmt--Capture all the columns to be pivotedSelect @sqlStmt = isnull(@sqlStmt + ' ','') + quotename(PropertyDesc) +',' from(select distinct PropertyDesc from Category where PropertyDesc <> '--' ) as SubTabset @sqlStmt = Left(@sqlStmt,len(@sqlStmt) -1)--Prepare a pivot statement to be executed dynamicallySet @PvtStmt = 'Select CatID, CatName , ' + @sqlStmt +'From(select CatID, CatName , PropertyDesc,PropertyValue from Category ) PPivot(Max(PropertyValue) for PropertyDescin ( ' + @sqlStmt + ')) as PvtOrder by CatID'exec (@PvtStmt)--Solution ends here--====================================================================Regards,BohraI am here to learn from Masters and help new bees in learning. |
|
|
keyursoni85
Posting Yak Master
233 Posts |
Posted - 2010-08-18 : 09:28:18
|
thank you bohra : ) |
|
|
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 |
|
|
|
|
|
|
|