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,

Data Creation:

if object_id('comment_text','U') is not null drop table comment_text
create table comment_text(text nvarchar(1000))
insert into comment_text values('1FOR DELIVERY ISSUES CONTACT ........ ')
insert into comment_text values('2AX ENT. ')
insert into comment_text values('3Person: Ashish')
select * from comment_text

Consider below query:

SELECT TEXT FROM COMMENT_TEXT

It gives the below output:

TEXT
---------------------------------------------------------------------------------------------
1FOR DELIVERY ISSUES CONTACT ........
2AX ENT.
3Person: 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
-----------------------------------------------------------------------------------------
1FOR DELIVERY          2AX ENT.               3Person: 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: 743 | Views in the last 30 days: 12
 
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...

ARTICLE

Invisible Comments

I ran into a dilemma when I was told that I should not allow potential competitors to view my JavaSc...

BLOG

Commenting TSQL Scripts

If you have ever searched for “commenting in TSQL” scripts, I am sure that you’ve found hundreds or ...

FORUM

Commenting

How much detail should be used in comments, do you assume a level of understanding? One comment I ...

FORUM

unable to see comments in store procedure

unable to see comments in store procedure

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