Transpose multiple rows into multiple columns in SQL Server
-
From: ObjectID - PropertyID - PropertyListValueID 1828 - 41 - 171 1828 - 41 - 170 1828 - 46 - 184 1828 - 47 - 189 To: ObjectID - 41 - 46 - 47 1828 - 170 - 184 - 189 1828 - 171 - 184 - 189 It's possible? Thanks
-
From: ObjectID - PropertyID - PropertyListValueID 1828 - 41 - 171 1828 - 41 - 170 1828 - 46 - 184 1828 - 47 - 189 To: ObjectID - 41 - 46 - 47 1828 - 170 - 184 - 189 1828 - 171 - 184 - 189 It's possible? Thanks
Which version of SQL Server? You could try PIVOT
-
From: ObjectID - PropertyID - PropertyListValueID 1828 - 41 - 171 1828 - 41 - 170 1828 - 46 - 184 1828 - 47 - 189 To: ObjectID - 41 - 46 - 47 1828 - 170 - 184 - 189 1828 - 171 - 184 - 189 It's possible? Thanks
-
With PIVOT i get: ObjectID - 41 - 46 - 47 -------------------------------------- 1828 - 171 - 184 - 189 or ObjectID - 41 - 46 - 47 -------------------------------------- 1828 - 171 - 184 - 189 1828 - 170 - NULL - NULL I must do: select ObjectID from table where [41]=171 and [46]=184 select ObjectID from table where [41]=170 and [46]=184
-
Which version of SQL Server? You could try PIVOT
Sql server 2012 pivot without aggregation
-
With PIVOT i get: ObjectID - 41 - 46 - 47 -------------------------------------- 1828 - 171 - 184 - 189 or ObjectID - 41 - 46 - 47 -------------------------------------- 1828 - 171 - 184 - 189 1828 - 170 - NULL - NULL I must do: select ObjectID from table where [41]=171 and [46]=184 select ObjectID from table where [41]=170 and [46]=184
Then you need to prepare your data differently, 46 and 47 do not have values for 170 so you need to add them to the result set. It is a very weird structure you are asking for where missing values reflect the previous value.
Never underestimate the power of human stupidity RAH
-
With PIVOT i get: ObjectID - 41 - 46 - 47 -------------------------------------- 1828 - 171 - 184 - 189 or ObjectID - 41 - 46 - 47 -------------------------------------- 1828 - 171 - 184 - 189 1828 - 170 - NULL - NULL I must do: select ObjectID from table where [41]=171 and [46]=184 select ObjectID from table where [41]=170 and [46]=184
hi, use this table as example:
COD surname name CODCAU from from_time to to_time period
1 ROSSI MARCO 301 19/09/2005 0.00 23/09/2005 0.00 p1
1 ROSSI MARCO 301 09/12/2005 0.00 09/12/2005 0.00 p2
1 BIANCHI FABIO 301 12/01/2004 0.00 16/01/2004 0.00 p1
1 BIANCHI FABIO 301 02/04/2004 0.00 02/04/2004 0.00 p2
1 BIANCHI FABIO 301 02/05/2004 0.00 10/05/2004 0.00 p3then to pivot:
select *
FROM
(
--concatenate to create a period on unique column...
SELECT surname,name,COD,from+' '+from_time+' '+to+' '+to_time as date,period
FROM [pivot]
) as s
PIVOT
(
--pivot aggregate by the columns different by 'date/period' -> (surname,name,COD)
max(date)
FOR period IN ([p1],[p2],[p3])
)AS p -
Then you need to prepare your data differently, 46 and 47 do not have values for 170 so you need to add them to the result set. It is a very weird structure you are asking for where missing values reflect the previous value.
Never underestimate the power of human stupidity RAH
I must filter/select every ObjectID with PropertyID=QueryString value and PropertyListValueID=QueryString value Initial structure: +----------+------------+---------------------+ | ObjectID | PropertyID | PropertyListValueID | +----------+------------+---------------------+ | 1828 | 41 | 171 | | 1828 | 41 | 170 | | 1828 | 46 | 184 | | 1828 | 47 | 189 | +----------+------------+---------------------+