Converting resultset in simple select statement

Question

Converting resultset in simple select statement

S_Kumar_S

SSCrazy Eights

Points: 9037
More actions
January 14, 2014 at 9:18 am

#303228

Hi
I have a resultset returned from a select statement :
Name FirstNamePhone
Test1-LTest1-FGermany
Test2-L Test2-FGermany
I want to convert it like :
SELECT 'Test1-L' Name ,'Test1-F' FirstName,'Germany' Phone
UNION
SELECT 'Test2-L','Test2-F','Germany'
Basically getting same resultset with plane values.
Appreciate help in advance.
Online Trainer For SQL DBA and Developer @RedBushTechnologies with 18 yrs exp.

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Sean Lange SSC Guru Points: 286575 More actions · Answer 1

S_Kumar_S (1/14/2014)
Hi
I have a resultset returned from a select statement :
Name FirstNamePhone
Test1-LTest1-FGermany
Test2-L Test2-FGermany
I want to convert it like :
SELECT 'Test1-L' Name ,'Test1-F' FirstName,'Germany' Phone
UNION
SELECT 'Test2-L','Test2-F','Germany'
Basically getting same resultset with plane values.
Appreciate help in advance.

Huh?

Can you provide a quick temp table and sample data and explain what you want as output?

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

x SSC-Insane Points: 23660 More actions · Answer 2

If we mean actually create select constants from table or query values:

CREATE TABLE #TEST

(

NAME VARCHAR(100),

FIRSTNAME VARCHAR(100),

PHONE VARCHAR(100)

)

INSERT INTO #TEST

SELECT 'Test1-L','Test1-F','Germany'

UNION

SELECT 'Test2-L','Test2-F','Germany'

UNION

SELECT 'Test3-L','Test3-F','Germany'

;

WITH SOURCEQ (ROWNUM, TXTWRK) AS

(SELECT ROW_NUMBER() OVER (ORDER BY NAME, FIRSTNAME, PHONE) ROWNUM,

'SELECT ''' + NAME + ''' Name,''' + FIRSTNAME + ''' Firstname,''' + PHONE + ''' Phone' TXTWRK

FROM #TEST)

SELECT TXTWRK + CASE WHEN ROWNUM = (SELECT COUNT(*) C FROM SOURCEQ) THEN '' ELSE ' UNION' END TXT FROM SOURCEQ

S_Kumar_S SSCrazy Eights Points: 9037 More actions · Answer 3

Thanks Much , it helped me fulfill my requirement..

Online Trainer For SQL DBA and Developer @RedBushTechnologies with 18 yrs exp.