Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
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: 867 | Views in the last 30 days: 6
 
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