Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

Get your favorite SSC scripts directly in SSMS with the free SQL Scripts addin. Search for scripts directly from SSMS, and instantly access any saved scripts in your SSC briefcase from the favorites tab.
Download now (direct download link)

Convert Row to Column without using any other column for Pivot

By ashish verma,

Consider below query:

SELECT TEXT FROM COMMENT_TEXT

It gives the below output:

TEXT
---------------------------------------------------------------------------------------------
FOR DELIVERY ISSUES CONTACT ........
AX ENT.
Person: Ashish

So result is a table having 3 rows data in a single column named TEXT.

Now aim is to convert this into 3 column data having column names TEXT1, TEXT2, TEXT3. TEXT2 and TEXT3, may or may not be present. TEXT1 will always have some data. Output should be:

TEXT1                           TEXT2                              TEXT3
-----------------------------------------------------------------------------------------
FOR DELIVERY            AX ENT.                  Person: Ashish   
ISSUES CONTACT
........                                                                                                                                                                                                                                                                                                                                                                                                                                                                                     
The below query can solve this:
SELECT  
TEXT1, isnull(TEXT2,'') as TEXT2,isnull(TEXT3,'') as TEXT3
FROM
(SELECT 'TEXT' + convert(nvarchar(1),ROW_NUMBER() over (ORDER BY TEXT)) as ID, TEXT 
    FROM COMMENT_TEXT) AS SourceTable
PIVOT
(max(TEXT)
FOR ID IN (TEXT1, TEXT2,TEXT3)
) AS PivotTable;

Total article views: 2231 | Views in the last 30 days: 7
 
Related Articles
SCRIPT

Convert Row to Column without using any other column for Pivot

Aim is to convert Row output having 3 rows of column Text to column output having 3 columns named as...

FORUM

CAN ANY ONE WRITE QUERY FOR BELOW DESCRIPTION!

BELOW IS THE ONE OF THE COLUMN DATA,FROM THAT WE HAVE WE HAVE TO PULL 'CA' DATA.

FORUM

reading columns

Hi How can I loop through columns in a record to find any value that's not null? I think I'll be us...

FORUM

Urgent help needed to convert 100 rows into 100 columns

I shuld convert 100 rows into 100 columns and display the value below each column.

FORUM

concatenations

Hi Need help with concatenation and pivoting: @table 1 is as follows ...

Tags
convert row to column    
pivot    
row_number    
 
Contribute

Join the most active online SQL Server Community

SQL knowledge, delivered daily, free:

Email address:  

You make SSC a better place

As a member of SQLServerCentral, you get free access to loads of fresh content: thousands of articles and SQL scripts, a library of free eBooks, a weekly database news roundup, a great Q & A platform… And it’s our huge, buzzing community of SQL Server Professionals that makes it such a success.

Join us!

Steve Jones
Editor, SQLServerCentral.com

Already a member? Jump in:

Email address:   Password:   Remember me: Forgotten your password?
Steve Jones