CODEWITHSUNDEEP
sqlsql-serversplit
user2151321
user2151321

Reputation: 11

Transposing a row to columns

I have imported this data into SQL Server from a flat file source.

B  01  1007282   Y  1001201 15102
B  02  LEVEL     Y  2705201 15102
B  03  1998014   Y  2808201 15102
B  05            Y  2808201 15102
B  06  49081100  Y  1708201 15102
B  07  64072151  Y  2903201 15102
B  08  75090350  Y  0111200 15102
B  09  58082950  Y  0608200 15102
B  10  75112551  Y  1007200 15102
B  11  72030950  Y  1007200 15102
B  20  74507632  Y  2808201 15102
B  23  98240166  Y  2808201 15102
B  25  U4507632  Y  2808201 15102
B  26  45002267  Y  2808201 15102

From this data I need to take each row and split each one into a different column:

Column 1 : B  01  1007282   Y  1001201 15102
Column 2 : B  02  LEVEL     Y  2705201 15102
...

Upvotes: 0

Views: 364

Answers (1)

Aaron Bertrand
Aaron Bertrand

Reputation: 280252

This answer may become obsolete depending on further requirements, but given the comments thus far, my suggestion is to not change the data in SQL Server just to review it horizontally rather than vertically. Applications like Excel are much better suited to this. You can copy and paste the query result from Management Studio (results to grid), copy and paste from the source flat file, or otherwise get the data into Excel as a column. Highlight the 200 rows, hit Copy, then right-click somewhere and choose Paste Special... you will see a dialog here, simply check the Transpose checkbox and click OK. Voila, your rows are columns!

enter image description here

enter image description here

Sorry for the size. My retina doubles the size of all screen shots, and I haven't found a convenient workaround yet, other than using a different computer. :-(

Now, if you really want to do this in SQL Server, you could I suppose do something ugly like this. Given the following source table (and assuming SQL Server 2008 or better):

CREATE TABLE dbo.InColumns(col VARCHAR(255));
GO

INSERT dbo.InColumns(col) VALUES
('B  01  1007282   Y  1001201 15102'),
('B  02  LEVEL     Y  2705201 15102'),
('B  03  1998014   Y  2808201 15102'),
('B  05            Y  2808201 15102'),
('B  06  49081100  Y  1708201 15102'),
('B  07  64072151  Y  2903201 15102'),
('B  08  75090350  Y  0111200 15102'),
('B  09  58082950  Y  0608200 15102'),
('B  10  75112551  Y  1007200 15102'),
('B  11  72030950  Y  1007200 15102'),
('B  20  74507632  Y  2808201 15102'),
('B  23  98240166  Y  2808201 15102'),
('B  25  U4507632  Y  2808201 15102'),
('B  26  45002267  Y  2808201 15102');

Now some oh so fun dynamic SQL:

DECLARE @sql NVARCHAR(MAX) = N'', @maxlen INT;

SELECT @maxlen = MAX(LEN(col)) FROM dbo.InColumns;

SELECT @sql += N',
    Col' + RTRIM(rn) + ' VARCHAR(' + RTRIM(@maxlen) + ')'
 FROM (SELECT rn = ROW_NUMBER() OVER 
  (ORDER BY col) FROM dbo.InColumns) AS x;

SET @sql = N'CREATE TABLE dbo.InRows
(' + STUFF(@sql, 1, 1, N'') + ');';

EXEC sp_executesql @sql;

SET @sql = N'';

SELECT @sql += N', 
  (SELECT col FROM x WHERE rn = ' + RTRIM(rn) + ')'
  FROM (SELECT rn = ROW_NUMBER() 
    OVER (ORDER BY col) FROM dbo.InColumns) AS x;

SET @sql = N';WITH x AS (SELECT col, rn = ROW_NUMBER() OVER 
   (ORDER BY col) FROM dbo.InColumns)
INSERT dbo.InRows SELECT TOP (1) '
  + STUFF(@sql, 1, 1, N'') + ' FROM x;';

EXEC sp_executesql @sql;
GO
SELECT * FROM dbo.InRows;
GO
DROP TABLE dbo.InRows;

Results:

Col1                               Col2   ...
---------------------------------  -------------------------
B  01  1007282   Y  1001201 15102  B  02  LEVEL     Y  27...

See why this is much easier in Excel?

With 200 rows, you're coming dangerously close to exceeding the max row size for a table. You could "fix" that by making these 200 varchar(max) columns, but the thought of that actually made me shudder.

Upvotes: 1

Related Questions