Move to new row

Question

Post reply

Move to new row

girl_bj

SSCommitted

Points: 1731
More actions
March 11, 2016 at 12:45 am

#316938

I'm having issue to move data to new row for my txt output.
sample data
resultA resultB resultC resultD
chicago ID123 20pax ID123
austria ID999 90pax ID999
Spain ID777 10pax ID777
i would like to make it group by row.
expexted date
result
chicago
20pax
austria
90pax
spain
10pax
is that possible?

Viewing 9 posts - 1 through 8 (of 8 total)

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

Hugo Kornelis SSC Guru Points: 64790 More actions · Answer 1

SELECT resultA

FROM YourTable

UNION ALL

SELECT resultC

FROM YourTable;

If that's not what you need, then please post a CREATE TABLE statement for the table, INSERT statements with sample data, expected results, and an explanation.

Hugo Kornelis, SQL Server/Data Platform MVP (2006-2016)
Visit my SQL Server blog: https://sqlserverfast.com/blog/
SQL Server Execution Plan Reference: https://sqlserverfast.com/epr/

girl_bj SSCommitted Points: 1731 More actions · Answer 2

girl_bj

SSCommitted

Points: 1731

March 13, 2016 at 8:07 pm

#1864828

I tried that way but not in order.

DesNorton SSC-Insane Points: 24399 More actions · Answer 3

This looks like a simple UNPIVOT requirement

CREATE TABLE #temp (

ID VARCHAR(100)

, ResultA VARCHAR(100)

, ResultB VARCHAR(100)

);

INSERT INTO #temp ( ID, ResultA, ResultB )

SELECT src.ID, src.ResultA, src.ResultB

FROM (VALUES ( 'ID123', 'chicago', '20pax' )

, ( 'ID999', 'austria', '90pax' )

, ( 'ID777', 'Spain' , '10pax' )

) AS src( ID, ResultA, ResultB );

SELECT Value

FROM

(

SELECT [ID]

, [ResultA]

, [ResultB]

FROM #temp

) AS srcData

UNPIVOT

(

Value FOR colName IN ( [ResultA], [ResultB] )

) AS rslt

ORDER BY ID;

How to post data/code on a forum to get the best help.

girl_bj SSCommitted Points: 1731 More actions · Answer 4

I'm getting this error.

Any idea?

The type of column "" conflicts with the type of other columns specified in the UNPIVOT list.

DesNorton SSC-Insane Points: 24399 More actions · Answer 5

girl_bj (3/14/2016)
The type of column "" conflicts with the type of other columns specified in the UNPIVOT list.

The datatypes of your source columns does not match. Find the biggest one, and cast the others to match that.

If you paste exactly what you typed above into Google, you will see a number of answers to your issue.

How to post data/code on a forum to get the best help.

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 6

;WITH sample_data (resultA, resultB, resultC, resultD) AS (

SELECT 'chicago', 'ID123', '20pax', 'ID123' UNION ALL

SELECT 'austria', 'ID999', '90pax', 'ID999' UNION ALL

SELECT 'Spain', 'ID777', '10pax', 'ID777')

SELECT x.newcol1, x.newcol2

FROM sample_data

CROSS APPLY (

VALUES (resultA, resultB, 1),

(resultC, resultD, 2)

) x (newcol1, newcol2, OrderBy)

ORDER BY newcol2, OrderBy

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

girl_bj SSCommitted Points: 1731 More actions · Answer 7

Thanks.

Is it possible to include in between new data?

Newrecord

chicago

20pax

Newrecord

Spain

10pax

Newrecord

austria

90pax

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 8

girl_bj (3/14/2016)
Thanks.
Is it possible to include in between new data?
Newrecord
chicago
20pax
Newrecord
Spain
10pax
Newrecord
austria
90pax

Sure:

;WITH sample_data (resultA, resultB, resultC, resultD) AS (

SELECT 'chicago', 'ID123', '20pax', 'ID123' UNION ALL

SELECT 'austria', 'ID999', '90pax', 'ID999' UNION ALL

SELECT 'Spain', 'ID777', '10pax', 'ID777')

SELECT x.newcol1 --, x.newcol2

FROM sample_data

CROSS APPLY (

VALUES

('Newrecord', resultB, 0),

(resultA, resultB, 1),

(resultC, resultB, 2)

) x (newcol1, newcol2, OrderBy)

ORDER BY newcol2, OrderBy

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden