Variable Question - Using row data to create a column

[giggles7840]

xp/xir2/sql server

I'm pretty sure this is a variable question and I am really bad at variables.

I need to get a value from a record and create a column using that value. The value can be associated with an ID for each record. I am just not sure how to go about doing it.

Example:
GH1 Title
GH2 Yes ID#1
GH2 No ID#2
GH2 Maybe ID#3
GH2 Yes ID#4
GH2 No ID#5
GH2 City1 ID#1
GH2 City2 ID#2
GH2 City1 ID#3
GH2 City2 ID#4
GH2 City3 ID#5

and what i need is:
GH1 Title
GH2 Yes ID#1 City1
GH2 No ID#2 City2
GH2 Maybe ID#3 City1
GH2 Yes ID#4 City2
GH2 No ID#5 City3
GH2 City1 ID#1 City1
GH2 City2 ID#2 City2
GH2 City1 ID#3 City1
GH2 City2 ID#4 City2
GH2 City3 ID#5 City3

Any help is appreciated.

 

[CaptainD]

Where are you getting your data from?

If it is SQL Server, why not create a view with an inner join onto itself with it joined on the "ID#_"


Not tested

SELECT t.Title, t.ID, tt.Title FROM YourTable
INNER JOIN (SELECT ID, TITLE FROM YourTable) AS tt
ON t.ID = tt.ID

I'm just learning Crystal so there might be another way but this was my first thought.

 

[giggles7840]

I am using SQL Server, however there is a universe built on top of it that I am using.

 

[IanWaterman]

What is the relationship between City and the rest of the data. I could not see any pattern

If there is some logic then a formula will work

@City

If (whatever condtion is true) then 'City1' else
If (another condition true) then 'City2 else

etc.

Ian

 

[giggles7840]

I need to identify the city for each ID. The problem is that the city is not its own column but a value in a column.

So, if ID 1234 = Dallas then every record with an ID of 1234 needs to be flagged as Dallas in its own column.

However, I do not want to hard code the ID, i need a way to have the ID value dynamic since they are continuosly created and not a static value.

 

[IanWaterman]

Sounds like you have a City table?

Why not just join that to the table holding your IDs, then you can just place the field on the report.

Ian

 

[giggles7840]

I dont have a city table.

 

[IanWaterman]

If you do not have a defined relationship in your dataset then you will have to hard code in a formula similar to the one I posted above.

Ian

 

[CaptainD]

I am using SQL Server, however there is a universe built on top of it that I am using.

Does that mean you can not create and use views?

From what has been posted, my suggestion would still work provided you have the ability to create the view.

 

[giggles7840]

i have a dba writing creating a new view for me.