Need Arrange Query result into another table Header
-
Heelo All, I want to arrange a result of query into Row header. Example : in one table i have the Names Of Books. I want to arrage each book name as heading in another Table. Select * from BookNames BookNames --------- Java Programming visual studio.net Operating Sytems ORDBMS System analysis and Design System Software I want to arrage the Result as a separate table Like Below JavaProgarmming visual studio.net OperatingSytems ORDBMS --------------- ----------------- ----------------- ------ IS it possible? Please Help me. Thanks RIZ.......
-
Heelo All, I want to arrange a result of query into Row header. Example : in one table i have the Names Of Books. I want to arrage each book name as heading in another Table. Select * from BookNames BookNames --------- Java Programming visual studio.net Operating Sytems ORDBMS System analysis and Design System Software I want to arrage the Result as a separate table Like Below JavaProgarmming visual studio.net OperatingSytems ORDBMS --------------- ----------------- ----------------- ------ IS it possible? Please Help me. Thanks RIZ.......
take a look at Pivot. Use that with a dynamic sql to construct your book colums. In this script the @List would bhe your book titles
BEGIN Set @SQL = 'SELECT PortfolioID, Portfolio, ' + @List + char(13) Set @SQL = @SQL + 'FROM (SELECT PortfolioID, Portfolio, A.Element, PFClass ' + char(13) Set @SQL = @SQL + 'FROM vwPFAttribute A ' + char(13) Set @SQL = @SQL + 'INNER JOIN PFTree T ON T.PFClassID = A.PFClassID AND T.TreeID = ' + CONVERT(VARCHAR(20),@TreeID) + char(13) Set @SQL = @SQL + 'WHERE A.PortfolioID NOT IN (SELECT PortfolioID FROM dbo.fn_TreeExcludedPortfolios(' + CONVERT(VARCHAR(20),@TreeID) + ')))P' + char(13) Set @SQL = @SQL + 'Pivot (Max(Element) For PFClass In (' + @List + ')) as Pvt' + char(13) Set @SQL = @SQL + 'Order By Portfolio' Print @SQL ENDNever underestimate the power of human stupidity RAH
-
take a look at Pivot. Use that with a dynamic sql to construct your book colums. In this script the @List would bhe your book titles
BEGIN Set @SQL = 'SELECT PortfolioID, Portfolio, ' + @List + char(13) Set @SQL = @SQL + 'FROM (SELECT PortfolioID, Portfolio, A.Element, PFClass ' + char(13) Set @SQL = @SQL + 'FROM vwPFAttribute A ' + char(13) Set @SQL = @SQL + 'INNER JOIN PFTree T ON T.PFClassID = A.PFClassID AND T.TreeID = ' + CONVERT(VARCHAR(20),@TreeID) + char(13) Set @SQL = @SQL + 'WHERE A.PortfolioID NOT IN (SELECT PortfolioID FROM dbo.fn_TreeExcludedPortfolios(' + CONVERT(VARCHAR(20),@TreeID) + ')))P' + char(13) Set @SQL = @SQL + 'Pivot (Max(Element) For PFClass In (' + @List + ')) as Pvt' + char(13) Set @SQL = @SQL + 'Order By Portfolio' Print @SQL ENDNever underestimate the power of human stupidity RAH
Mycroft Holmes wrote:
look at Pivot.
Seems like alot of questions lately floating around PIVOTs :suss:
"The clue train passed his station without stopping." - John Simmons / outlaw programmer "Real programmers just throw a bunch of 1s and 0s at the computer to see what sticks" - Pete O'Hanlon
-
Mycroft Holmes wrote:
look at Pivot.
Seems like alot of questions lately floating around PIVOTs :suss:
"The clue train passed his station without stopping." - John Simmons / outlaw programmer "Real programmers just throw a bunch of 1s and 0s at the computer to see what sticks" - Pete O'Hanlon
Nasty bloody things until you get the hang of them. Once you get the idea that there are inner and outer queries it starts to fall into place. I used to do a lot of horrible cursor and while selects to get the same result until 2005. I broke down into 3 steps 1 Build your query to the minimum of data you need (inner query) 2 build your column string = @List 3 wrap these in the pivot query
Never underestimate the power of human stupidity RAH
-
take a look at Pivot. Use that with a dynamic sql to construct your book colums. In this script the @List would bhe your book titles
BEGIN Set @SQL = 'SELECT PortfolioID, Portfolio, ' + @List + char(13) Set @SQL = @SQL + 'FROM (SELECT PortfolioID, Portfolio, A.Element, PFClass ' + char(13) Set @SQL = @SQL + 'FROM vwPFAttribute A ' + char(13) Set @SQL = @SQL + 'INNER JOIN PFTree T ON T.PFClassID = A.PFClassID AND T.TreeID = ' + CONVERT(VARCHAR(20),@TreeID) + char(13) Set @SQL = @SQL + 'WHERE A.PortfolioID NOT IN (SELECT PortfolioID FROM dbo.fn_TreeExcludedPortfolios(' + CONVERT(VARCHAR(20),@TreeID) + ')))P' + char(13) Set @SQL = @SQL + 'Pivot (Max(Element) For PFClass In (' + @List + ')) as Pvt' + char(13) Set @SQL = @SQL + 'Order By Portfolio' Print @SQL ENDNever underestimate the power of human stupidity RAH
Sir, That query returns a below error in Parse. am using sqlserver 2005. ' Must Declare a Scalar variable @SQL' ' Must declare a Scalar Variable @OList' What can i Do Now? This is sample Program only, hardly i will use only 10 to 15 books only. because this is my mini project. Please help me
-
Sir, That query returns a below error in Parse. am using sqlserver 2005. ' Must Declare a Scalar variable @SQL' ' Must declare a Scalar Variable @OList' What can i Do Now? This is sample Program only, hardly i will use only 10 to 15 books only. because this is my mini project. Please help me
Astonishing - you actually thought that query would run right, it was there as an example.
rrrriiizz wrote:
What can i Do Now?
Read the other replies, do some research in BOL on pivot queries, find out what dynamic SQL is. You will not be spoon fed with code, we are here to help you increase your skills and LEARN, not do your job for you. I strongly suggest you get a book in SQL Server and start from there.
Never underestimate the power of human stupidity RAH
-
It is to a certain extent, but what would happen if you had say 1000 booknames? You would not want 1000 columns - and most versions of RDBMS wouldn't allow that many any way.
Bob Ashfield Consultants Ltd
-
Heelo All, I want to arrange a result of query into Row header. Example : in one table i have the Names Of Books. I want to arrage each book name as heading in another Table. Select * from BookNames BookNames --------- Java Programming visual studio.net Operating Sytems ORDBMS System analysis and Design System Software I want to arrage the Result as a separate table Like Below JavaProgarmming visual studio.net OperatingSytems ORDBMS --------------- ----------------- ----------------- ------ IS it possible? Please Help me. Thanks RIZ.......
Hi there!!!, I have one idea for your question.. since this is ur small project try to do it in this way... this is just an alternative way.. Use your front end to for creation of tables... As you said this may have hardly 15 to 20 records... Assume that you are using VB.Net as your front end,, then use a for loop to loop through the values underit use table creation script which would greate dynamicly... based on the values. Hope this gives you a sight!!! Cheers, ZAK
ZAK
-
Hi there!!!, I have one idea for your question.. since this is ur small project try to do it in this way... this is just an alternative way.. Use your front end to for creation of tables... As you said this may have hardly 15 to 20 records... Assume that you are using VB.Net as your front end,, then use a for loop to loop through the values underit use table creation script which would greate dynamicly... based on the values. Hope this gives you a sight!!! Cheers, ZAK
ZAK
-
You are welcome dude!!!!!
ZAK