Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

T-SQL query help Expand / Collapse
Author
Message
Posted Wednesday, September 26, 2012 12:19 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Wednesday, September 10, 2014 12:03 PM
Points: 74, Visits: 387
Hi..
I have data the data in the below temp table(SQL query)
IF object_id('TempDB..#tTemp1') IS NOT NULL
DROP TABLE #tTemp1
CREATE TABLE #tTemp1(ID int, Name varchar(20))
INSERT INTO #tTemp1(ID, Name)
SELECT 1, 'NewYork'
UNION
SELECT 2, 'Virginia'
UNION
SELECT 3, 'London'
UNION
SELECT 4, 'Sydney'
UNION
SELECT 5, 'Dubai'

SELECT * FROM #tTemp1
DROP TABLE #tTemp1



I need the SQL query to get the output as:


Thanks..


  Post Attachments 
Image.png (133 views, 6.97 KB)
Post #1364451
Posted Wednesday, September 26, 2012 12:36 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: 2 days ago @ 7:09 AM
Points: 878, Visits: 2,395
Its really a simple solution with all you need to do is add a column that conactinates the fixed string 'This city name is ' and the Name Column.





_________________________________________________________________________
SSC Guide to Posting and Best Practices
Post #1364455
Posted Wednesday, September 26, 2012 12:55 AM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Today @ 5:21 PM
Points: 20,676, Visits: 32,263
Yep, just this easy:


IF object_id('TempDB..#tTemp1') IS NOT NULL
DROP TABLE #tTemp1
CREATE TABLE #tTemp1(ID int, Name varchar(20))
INSERT INTO #tTemp1(ID, Name)
SELECT 1, 'NewYork'
UNION
SELECT 2, 'Virginia'
UNION
SELECT 3, 'London'
UNION
SELECT 4, 'Sydney'
UNION
SELECT 5, 'Dubai'

SELECT ID, Name, 'The city name is ' + Name as Comment FROM #tTemp1
DROP TABLE #tTemp1





Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Post #1364459
Posted Wednesday, September 26, 2012 1:38 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Yesterday @ 5:15 PM
Points: 1,945, Visits: 3,008
Did you know that in any tiered architecture, not just SQL, that kind of display formatting is always done in a presentation layer? This tiered concept is usually covered in the first day of any class on the subject.

Books in Celko Series for Morgan-Kaufmann Publishing
Analytics and OLAP in SQL
Data and Databases: Concepts in Practice
Data, Measurements and Standards in SQL
SQL for Smarties
SQL Programming Style
SQL Puzzles and Answers
Thinking in Sets
Trees and Hierarchies in SQL
Post #1364483
Posted Thursday, September 27, 2012 4:01 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, February 27, 2014 6:05 AM
Points: 43, Visits: 162
Hi ,

You can get the desired result quiet easily...

The below mentioned code will solve your problem.

Select ID, Name, 'The City name is ' + Name Comment
From #tTemp1
Post #1365124
Posted Thursday, September 27, 2012 7:13 PM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Yesterday @ 3:25 AM
Points: 3,417, Visits: 5,328
Another way is like this (a computed column):

IF object_id('TempDB..#tTemp1') IS NOT NULL
DROP TABLE #tTemp1

CREATE TABLE #tTemp1(ID int, Name varchar(20)
,Comment AS ('The city name is ' + Name))
INSERT INTO #tTemp1(ID, Name)
SELECT 1, 'NewYork'
UNION
SELECT 2, 'Virginia'
UNION
SELECT 3, 'London'
UNION
SELECT 4, 'Sydney'
UNION
SELECT 5, 'Dubai'

SELECT * FROM #tTemp1
DROP TABLE #tTemp1





My mantra: No loops! No CURSORs! No RBAR! Hoo-uh!

My thought question: Have you ever been told that your query runs too fast?

My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.


Need to UNPIVOT? Why not CROSS APPLY VALUES instead?
Since random numbers are too important to be left to chance, let's generate some!
Learn to understand recursive CTEs by example.
Splitting strings based on patterns can be fast!
Post #1365570
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse