How to get rows into columns into SQL Server

Question

I need a small help. I want to convert my result of SQL into single row.

Lets say there is a table Students with ID and Name in it.

if I execute query

select * from Students

it returns.

Col1  Col2
1     Rizwan
2     Ahmed

I want result to be like

1    Rizwan    2   Ahmed

Please note that I want every record in a separate column.

Thanks in advance

This isn't going to scale very well if, for example, you have hundreds of students. Perhaps there is another way to accomplish what you want? — Tim Biegeleisen, Dec 08 '16 at 06:31
yes then there will be 1000 columns . But in my case there will be max 10,12 students — netboy 1989, Dec 08 '16 at 06:35
Out of curiosity, why? This sounds like a presentation-layer concern. Only transpose the data directly in your queries if it has a data-semantic purpose. — Dai, Dec 08 '16 at 06:42
I dont want to flip the result. I want it to be in single row — netboy 1989, Dec 08 '16 at 06:43
This can only be done for a constant number of rows if you're not willing to use dynamic SQL, and even with dynamic SQL this is a massive pain (and no more than 1024 columns are allowed in a table). If this is an ad-hoc query and you have Excel, that is far easier to use as it has a "transpose" function on copy-paste. — Jeroen Mostert, Dec 08 '16 at 06:45
Where do you want to show data? If you use front end application, do this there — Madhivanan, Dec 08 '16 at 06:46
Actually requirement is that i need to extract data using single SQL — netboy 1989, Dec 08 '16 at 06:52
Mean i cannot handle this on application level . I need to write a SQL query which will return me desired single row result — netboy 1989, Dec 08 '16 at 07:02
[Dynamic Pivot in SQL](http://stackoverflow.com/q/10404348/243373). Read how it's done there. — TT., Dec 08 '16 at 08:22

Dai · Answer 1 · 2016-12-08T07:28:49.393

-1

I can't think of a plausible scenario where this transform serves any useful purpose because relational-algebra is, by design, about sets of data sharing the same attributes (i.e. tables have rows with columns) - by having everything in a single row with meaningless columns you're just effectively serializing data into a blob.

The only way to achieve this is using Dynamic SQL, as this is the only way to achieve a dynamic number of columns without prior knowledge of what columns are desired.

In MS SQL Server you might think of using PIVOT/UNPIVOT but the columns still need to be manually named, thus requiring Dynamic SQL.

MySQL Server (and MariaDB) have GROUP_CONCAT which can be used to combine multiple rows into a single string (text) value but the server lacks any kind of "split" function. So GROUP_CONCAT doesn't work here because it doesn't return discrete columns.

In T-SQL (MS SQL Server, Sybase) you need to iterate over every target row, this is done using a CURSOR. You cannot reliably perform string concatenation inside a SELECT statment:

DECLARE @sql nvarchar(max) = 'SELECT '

DECLARE c CURSOR FOR
    SELECT [Id], [Name] FROM Students ORDER BY [Id] ASC

OPEN c

DECLARE @id   int
DECLARE @name nvarchar(100)

FETCH NEXT FROM c INTO @id, @name

WHILE @@FETCH_STATUS = 0
BEGIN

    SET @sql = @sql + CONVERT( varchar(10), @id ) + ', ' + @name

    FETCH NEXT FROM c INTO @id, @name
END

CLOSE c
DEALLOCATE c

sp_executesql @sql -- this will execute the `SELECT` that was generated, where each discrete value will be returned as an anonymous column.

edited Dec 08 '16 at 07:28

answered Dec 08 '16 at 07:06

Dai

141,631
28
261
374

1

`FOR XML PATH('')`, while at times clumsy and certainly far from efficient, is still a reliable way to do string concatenation in a `SELECT`. Among many other ways of doing that, so I take issue with the assertion that it isn't possible. See also [this question](http://stackoverflow.com/questions/451415). For bonus points, you can use `sys.columns` to construct this query regardless of the shape of the source table. (Personally I won't bother since this is a terrible thing to do anyway, but hey.) – Jeroen Mostert Dec 08 '16 at 07:13
That and the "cumulative select" trick... though I prefer the `FOR XML PATH('')` for group concat. Next version of SQL Server (2018?) will thankfully offer [STRING_AGG](https://msdn.microsoft.com/en-us/library/mt790580.aspx) so that we can finally be done with all the trickery. – TT. Dec 08 '16 at 07:24
*You cannot reliably perform string concatenation inside a SELECT statment:* >> Wrong and therefore -1. – TT. Dec 08 '16 at 07:42
@TT. I do not get consistent results when I use `SELECT @str = @str + Column FROM table`. Do you have an authoritative source that suggests using that approach is best-practice? – Dai Dec 08 '16 at 08:23
@Dai Best practice IMO is `STUFF((... FOR XML PATH(''), TYPE).value('.[1]','VARCHAR(MAX)'),1,1,'')`. The cumulative select requires a check to see if `@str` is empty or null depending on how you initialize it. Plus you can't use a cumulative select as a subquery. So the XML PATH currently is what is used in cases like that. Until `STRING_AGG` is implemented. – TT. Dec 08 '16 at 08:29
@Dai Explanation here: [How Stuff and 'For Xml Path' work in Sql Server](http://stackoverflow.com/q/31211506/243373). My own shameless plug: [How FOR XML PATH('') works when concatenating rows](http://stackoverflow.com/a/35172160/243373). – TT. Dec 08 '16 at 08:34
@Dai I'm at work, can only comment. Also, that's not how it's supposed to be right? Me working out an answer in your name? Try it out, change your answer, drop me a comment, I'll change my - to +. – TT. Dec 08 '16 at 08:43

How to get rows into columns into SQL Server

1 Answers1