Need help on Query

[CTl06]

Hello all,
I have a table with data in the format below

Rowid Value
1 William
2 John
3 CA
4 89788
5 Los Angeles.


What is the best way of writing aquery that flat this record out to one row. Example John William CA 89788 etc.

thank you much for your help.

 

[gmmastros]

Like this....

Declare @Temp Table(RowId Int, Value VarChar(50))

Insert Into @Temp Values(1,'William')
Insert Into @Temp Values(2,'John')
Insert Into @Temp Values(3,'CA')
Insert Into @Temp Values(4,'89788')
Insert Into @Temp Values(5,'Los Angeles.')

SET NOCOUNT ON
Declare @Output VarChar(1000)
Set @Output = ''

Select @Output = @Output + Value + ' '
From   @Temp
Where  Value Is Not NULL
Order By RowId

Select @Output

Notice that I put your sample data in to a table variable. This means that you can copy paste this to Query Analyzer and run it. If you are satisfied with it, you will need to modify the Select part to use your table instead of the table variable.

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[CTl06]

How do I do so I can assign each value with a column it. when i run the query it will return as below
First Last City State
John William Los Angeles CA


thanks

 

[gmmastros]

Maybe this...

Declare @Temp Table(RowId Int, Value VarChar(50))

Insert Into @Temp Values(1,'William')
Insert Into @Temp Values(2,'John')
Insert Into @Temp Values(3,'CA')
Insert Into @Temp Values(4,'89788')
Insert Into @Temp Values(5,'Los Angeles.')

SET NOCOUNT ON

Select Min(Case When RowId = 2 Then Value End) As First,
       Min(Case When RowId = 1 Then Value End) As Last,
       Min(Case When RowId = 5 Then Value End) As City,
       Min(Case When RowId = 3 Then Value End) As State,
       Min(Case When RowId = 4 Then Value End) As Zip
From   @Temp

By the way... this is a really bad way to store your data.

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[CTl06]

George,
I know, this is database from an application that we bought on to our company.

 

[CTl06]

George based on that query it is always give me one row. but data should always return me multiple rows example.
Rowid Value
1 William
2 John
3 CA
4 89788
5 Los Angeles.
6 John
7 Doe
8 TX
9 Dallas
10 9999999

Expected results:

First Last City State
John William Los Angeles CA
John Doe Dallas TX

 

[SQLSister]

how do you know which city belongs to John William vice John Doe? There is nothing inthe structure to associate them.

Personally I would return the software to the company I bought it from and buy someone else's software. This software is likely to have severe data integrity issues as well as be a performance hog and difficult to write queries and reports against.

"NOTHING is more important in a database than integrity." ESquared

 

[ca8msm]

What is the software called?

Maybe they have a reporting tool or support for this type of query.


-------------------------------------------------------

Mark,
[URL unfurl="true"]http://aspnetlibrary.com[/url]
[URL unfurl="true"]http://mdssolutions.co.uk[/url] - Delivering professional ASP.NET solutions
[URL unfurl="true"]http://weblogs.asp.net/marksmith[/url]

 

[SQLDenis]

>>this is database from an application that we bought on to our company.

No, this is not a database, it is a flat file in disquise. Where are the keys, RI, constraints etc etc etc
are there gaps in the Rowid? if not then it looks like you can use rowid + 5 to get a complete row and loop through the whole pseudo-table you got

Denis The SQL Menace
--------------------
SQL Server Code,Tips and Tricks, Performance Tuning
SQLBlog.com, Google Interview Questions

 

[CTl06]

this is application custom application that we bought on from another company.. it is a piece of Junk.

 

[gmmastros]

From your sample data, there doesn't appear to be any valid way to distinguish the contents of a row.

Specifically, the first 5 rows have zipcode as the 4th row, but the next 5 have it as the 5th row.

I can't see any discernible pattern for how the data is stored.

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[ca8msm]

I think you need to go back to the company you bought the software from. Maybe they can explain how their structure works.


-------------------------------------------------------

Mark,
[URL unfurl="true"]http://aspnetlibrary.com[/url]
[URL unfurl="true"]http://mdssolutions.co.uk[/url] - Delivering professional ASP.NET solutions
[URL unfurl="true"]http://weblogs.asp.net/marksmith[/url]

 

[CTl06]

Thanks for your suggestions and your help guys..