July 27, 2013 at 10:40 pm
Comments posted to this topic are about the item Convert Row to Column without using any other column for Pivot
July 29, 2013 at 1:23 am
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
July 29, 2013 at 2:21 am
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:
July 29, 2013 at 3:31 am
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
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
July 29, 2013 at 2:33 pm
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
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply