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
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
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.
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.