Re: Need Arrange Query result into another table Header - Database Discussion Boards

Need Arrange Query result into another table Header

rrrriiizz19-Jul-08 1:45

19-Jul-08 1:45

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.......

Re: Need Arrange Query result into another table Header

Ashfield20-Jul-08 8:06

Ashfield

20-Jul-08 8:06

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

Re: Need Arrange Query result into another table Header

rrrriiizz20-Jul-08 22:54

rrrriiizz

20-Jul-08 22:54

Sir,
This is my Mini Project I will only use 10 to 15 book's Only. Once i add a book name in One table, it will automatically add as another tables header. Please help me if there is any way?

Thanks
Fathima

Re: Need Arrange Query result into another table Header

Mycroft Holmes20-Jul-08 14:47

Mycroft Holmes

20-Jul-08 14:47

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

<br />
BEGIN<br />
	Set @SQL = 'SELECT PortfolioID, Portfolio, ' + @List + char(13) <br />
	Set @SQL = @SQL + 'FROM (SELECT PortfolioID, Portfolio, A.Element, PFClass ' + char(13) <br />
	Set @SQL = @SQL + 'FROM vwPFAttribute A ' + char(13) <br />
	Set @SQL = @SQL + 'INNER JOIN PFTree T ON T.PFClassID = A.PFClassID AND T.TreeID = ' + CONVERT(VARCHAR(20),@TreeID) + char(13) <br />
	Set @SQL = @SQL + 'WHERE A.PortfolioID NOT IN (SELECT PortfolioID FROM dbo.fn_TreeExcludedPortfolios(' + CONVERT(VARCHAR(20),@TreeID) + ')))P' + char(13) <br />
<br />
	Set @SQL = @SQL + 'Pivot (Max(Element) For PFClass In ('  + @List + ')) as Pvt' + char(13) <br />
	Set @SQL = @SQL + 'Order By Portfolio'<br />
<br />
	Print @SQL<br />
END<br />
<br />

Never underestimate the power of human stupidity
RAH

Re: Need Arrange Query result into another table Header

Paul Conrad20-Jul-08 14:55

Paul Conrad

20-Jul-08 14:55

Mycroft Holmes wrote:
look at Pivot.

Seems like alot of questions lately floating around PIVOTs Suspicious | :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

Re: Need Arrange Query result into another table Header

Mycroft Holmes20-Jul-08 15:08

Mycroft Holmes

20-Jul-08 15:08

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

Re: Need Arrange Query result into another table Header

rrrriiizz20-Jul-08 22:12

rrrriiizz

20-Jul-08 22:12

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

Re: Need Arrange Query result into another table Header

Mycroft Holmes20-Jul-08 22:21

Mycroft Holmes

20-Jul-08 22:21

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

Re: Need Arrange Query result into another table Header

zahedonline21-Jul-08 2:03

zahedonline

21-Jul-08 2:03

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

Re: Need Arrange Query result into another table Header

rrrriiizz21-Jul-08 23:52

rrrriiizz

21-Jul-08 23:52

Thank You So Much, this was working good in my Project. Thanks a lotttttttttt....

Re: Need Arrange Query result into another table Header

zahedonline23-Jul-08 1:42

zahedonline

23-Jul-08 1:42

You are welcome dude!!!!!

ZAK

setup and deployment sql server 2005 database along with Setup file

vishnukamath19-Jul-08 1:06

vishnukamath

19-Jul-08 1:06

we developed windows application using C#,and we created setup file(Using Installer Class also ) for this ,this setup runnig succes fully in my machine ,but problem it was not runnig in Client Machine ,it is showing error " login Failed User is Not Trusted user " . How Can Connect client Sql Server 2005 .Could u Please help me.

Re: setup and deployment sql server 2005 database along with Setup file

SimulationofSai19-Jul-08 4:30

SimulationofSai

19-Jul-08 4:30

From the error message, I presume you're using Windows Authentication to connect to SQL Server. You have two options here,
1) Add the client's login as a trusted user in SQL Server. (This will only work if you're in domain and the client is also a user in the same domain)
2) Use SQL Server Authentication. (Use a username and password to connect to SQL Server)

Thanks.

Re: setup and deployment sql server 2005 database along with Setup file

zahedonline21-Jul-08 2:07

zahedonline

21-Jul-08 2:07

and also try this one...

http://www.banmanpro.com/support/sql2k.asp[^]

Hope this helps you Wink | ;)

ZAK

How i cast or convert Varchar column Data into Datetime

Rameez Raja18-Jul-08 23:57

Rameez Raja

18-Jul-08 23:57

hi,

can any one help me that how i convert already stored date value into varchar Data type into Datetime data type .

i have date in this format dd/MM/yyyy and is stored in varchar Data type how i convert whole dates data into datetime in above format .

i want to store only Date portion .iam using sql server 2000 data base .

Regards
Rameez

Re: How i cast or convert Varchar column Data into Datetime

Blue_Boy19-Jul-08 0:55

Blue_Boy

19-Jul-08 0:55

select convert(DateTime,dateColumn) from TableName

I Love T-SQL
"Don't torture yourself,let the life to do it for you."

sql query

ptvce18-Jul-08 23:39

ptvce

18-Jul-08 23:39

i have 2 table in sqlserver. i want do this command
insert into dabirestan (amuzeshgah) values (select amuzeshgah from dabirestan1)

i want fill the record of table dabirestan1 to table dabirestan

i want write a query. how is my query cammand. do any body know?

Re: sql query

Blue_Boy19-Jul-08 0:54

Blue_Boy

19-Jul-08 0:54

try
insert into dabirestan (amuzeshgah) select amuzeshgah from dabirestan1

I Love T-SQL
"Don't torture yourself,let the life to do it for you."

Re: sql query

zahedonline21-Jul-08 2:10

zahedonline

21-Jul-08 2:10

What Blue_Boy suggested was 100% right in addition to it please make sure that the Second table you use should have the same Design or Table structure of what you are trying to copy to... otherwise the query would fail... Laugh | :laugh:

Cheers,

ZAK

Hai

jeena994022076518-Jul-08 19:26

jeena9940220765

18-Jul-08 19:26

Very excellent performance.. but i cant connect with database..

database error was displayed..

Microsoft OLEDB provider for ODBC drivers(0x80004005)

Re: Hai

N a v a n e e t h18-Jul-08 20:02

N a v a n e e t h

18-Jul-08 20:02

jeena9940220765 wrote:
Very excellent performance.. but i cant connect with database..

database error was displayed..

Microsoft OLEDB provider for ODBC drivers(0x80004005)

Are you OK ? Please read this[^]

All C# applications should call Application.Quit(); in the beginning to avoid any .NET problems.- Unclyclopedia

How to use google | Ask smart questions

Re: Hai

zahedonline21-Jul-08 2:13

zahedonline

21-Jul-08 2:13

Please reffer to http://access.databases.aspfaq.com/why-do-i-get-general-error-unable-to-open-registry-key-driverid.html[^]

For your answer Wink | ;)

Cheers,

ZAK

Save results from table

Waheed Ur Rehman18-Jul-08 19:22

Waheed Ur Rehman

18-Jul-08 19:22

Hi all
I am working with SQL Server express edition. I want to save the results of my query in a text file.
SELECT UserName,JoinDate,PostCount FROM Posts
i have written this query in a SP. What i want is when i call the procedure the results should be saved in a text file.
Thanks

Re: Save results from table

Parwej Ahamad19-Jul-08 4:07

Parwej Ahamad

19-Jul-08 4:07

Do some research on CLR Stored procedure.

Parwej Ahamad
R & D: REST services with WCF

Re: Save results from table

SimulationofSai19-Jul-08 4:33

SimulationofSai

19-Jul-08 4:33

If you're using SSMS to connect to your SQL Server express instance, hit Ctrl + Shift + F and execute your SP.

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.