SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Convert Row to Column without using any other column for Pivot


Convert Row to Column without using any other column for Pivot

Author
Message
ashish.pisces
ashish.pisces
Grasshopper
Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)

Group: General Forum Members
Points: 24 Visits: 18
Comments posted to this topic are about the item Convert Row to Column without using any other column for Pivot
Michael Meierruth
Michael Meierruth
SSCarpal Tunnel
SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)

Group: General Forum Members
Points: 4828 Visits: 2526
I honestly don't understand what you are trying to achieve. Please explain yourself a little better using the sample code below. It does not produce the output you are showing. Furthermore, I don't understand the meaning of the output you are trying to obtain.


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('FOR DELIVERY ISSUES CONTACT ........ ')
insert into comment_text values('AX ENT. ')
insert into comment_text values('Person: Ashish')
select * from comment_text

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


Andrew Diniz
Andrew Diniz
SSC Eights!
SSC Eights! (996 reputation)SSC Eights! (996 reputation)SSC Eights! (996 reputation)SSC Eights! (996 reputation)SSC Eights! (996 reputation)SSC Eights! (996 reputation)SSC Eights! (996 reputation)SSC Eights! (996 reputation)

Group: General Forum Members
Points: 996 Visits: 293
I agree with Michael. This does not return the results as described. Furthermore, when TEXT contains NULL values, TEXT1 will be NULL. TEXT2 and TEXT3 will hold empty strings when there are second and third NULL values.
It's hard to think of a scenario when this might be useful. Presumably COMMENT_TEXT would have more than just three rows, right? Blink

Without further details, this serves only as an example of how-not-to-post. Still, it may be immortalised in one of Mr Celko's books. Laugh
ChrisM@Work
ChrisM@Work
SSC Guru
SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)

Group: General Forum Members
Points: 87112 Visits: 20548
It's not a brilliant script with the errors as they are: if you were to correct the ORDER BY (or better still, introduce a PK) and remove the ISNULLs from the output, it would make more sense to folks stumbling across it.

Pivot query:
SELECT  
RowID1,
RowID2,
RowID3,
RowID4
FROM (
SELECT RowID = 'RowID'+convert(nvarchar(1),ROW_NUMBER() OVER (ORDER BY (SELECT NULL))), TEXT
FROM COMMENT_TEXT
) AS SourceTable
PIVOT (MAX(TEXT) FOR RowID IN (RowID1, RowID2, RowID3, RowID4)) AS PivotTable

Equivalent crosstab query:
SELECT
RowID1 = MAX(CASE WHEN RowID = 'RowID1' THEN TEXT ELSE NULL END),
RowID2 = MAX(CASE WHEN RowID = 'RowID2' THEN TEXT ELSE NULL END),
RowID3 = MAX(CASE WHEN RowID = 'RowID3' THEN TEXT ELSE NULL END),
RowID4 = MAX(CASE WHEN RowID = 'RowID4' THEN TEXT ELSE NULL END)
FROM (
SELECT RowID = 'RowID'+convert(nvarchar(1),ROW_NUMBER() OVER (ORDER BY (SELECT NULL))), TEXT
FROM COMMENT_TEXT
) d



“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
Exploring Recursive CTEs by Example Dwain Camps
ashish.pisces
ashish.pisces
Grasshopper
Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)

Group: General Forum Members
Points: 24 Visits: 18
Hey guys, This query only serves the purpose to use Pivot without actually using any other column. Suppose you just have some rows returned from anywhere, and you just wish to show all of them (if more than 3, then modify query accordingly by adding more column names) in a columns view. I know it is just a newbie thing, but i thought this was interesting to introduce a new column by adding ROW_NUMBER and use that column in PIVOT instead of using any other column from actual table.
The query is working fine, just a sort of data was the problem. Below query solves this. I just added 1,2,3 to the data inserted to force the sorting:

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

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
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum







































































































































































SQLServerCentral


Search