Pivot this 2 Columns like this

image

Hi,
how can i bring this Dataset to pivot-form?
I have multiple Records with ColName = Col1,Col2,Col3 - and the value behind.
Thanks and Regards
Nicole

If i use Min/Max then i only get one row ;(

SELECT
*
FROM dbo.Test
PIVOT (MIN(ColValue)
FOR ColName in ([Col1],[Col2],[Col3] )) AS pvt

hi Nicole

:slight_smile:
:slight_smile:

Tried to do this

> without

using PIVOT

drop create data
use tempdb 

go 


drop table data
go 

create table data
(
colname varchar(100) NULL, 
colValue varchar(100) NULL
)
go 

insert into data select 'col1','A'
insert into data select 'col2','B'
insert into data select 'col3','C'
insert into data select 'col1','M'
insert into data select 'col2','N'
insert into data select 'col3','O'

select * from data 
go
SQL
; WITH cte 
     AS (SELECT CASE 
                  WHEN colname = 'col1' THEN colvalue 
                END AS col1, 
                CASE 
                  WHEN colname = 'col2' THEN colvalue 
                END AS col2, 
                CASE 
                  WHEN colname = 'col3' THEN colvalue 
                END AS col3 
         FROM   data), 
     cte1 
     AS (SELECT Row_number() 
                  OVER( 
                    ORDER BY (SELECT NULL)) AS rn, 
                col1 
         FROM   cte 
         WHERE  col1 IS NOT NULL), 
     cte2 
     AS (SELECT Row_number() 
                  OVER( 
                    ORDER BY (SELECT NULL)) AS rn, 
                col2 
         FROM   cte 
         WHERE  col2 IS NOT NULL), 
     cte3 
     AS (SELECT Row_number() 
                  OVER( 
                    ORDER BY (SELECT NULL)) AS rn, 
                col3 
         FROM   cte 
         WHERE  col3 IS NOT NULL) 
SELECT col1, 
       col2, 
       col3 
FROM   cte1 a 
       JOIN cte2 b 
         ON a.rn = b.rn 
       JOIN cte3 c 
         ON a.rn = c.rn
Results

image

1 Like

Hi, this works fine :slight_smile: thank you

However you do this, it will fail eventually because you have absolutely no data to determine which rows make up each column set. You cannot rely on the "natural order" in the table because, with the exception of a single very special case (and this isn't that case), the optimizer does not take the order of rows into consideration when return rows.

You MUST have a 3rd column that provides the grouping for the column sets or you'll eventually have a "silent failure" that will put the screws to you.

thanks jeff

i think i understand what you mean

could please help me understand better
thanks

> you cannot rely on the "natural order" in the table because, with the exception of a single very special case (and this isn't that case),

what is that very special case ..
:slight_smile:
:slight_smile:

The "Very Special Case" is a thing called the "Quirky Update" and there are some fairly important rules to follow if you want it to work correctly. It would take writing an article on the subject and I just don't have the time to do that here. It is a fairly common subject that you can Google for but understand that it offends a whole lot of people's sensitivities and they don't actually know how to use it correctly The key to it is to force an ordered scan of the Clustered Index and to prevent any parallelism. It's also good to build in a "safety" counter just in case MS someday changes the way Clustered Indexes and Updates work. The method has been available since the first days of Sybase/T-SQL and it hasn't changed in decades, though, and Microsoft isn't going to fix Clustered Indexes or Updates because they're not broke and there's no marketing value in changing something that's not broke. Instead, they built things like "Column Store" indexes.

Jeff, I am offended please apologize to me :joy:

:yum: