Menu
How can I get second table from first table using TRANSFORM and PIVOT functions:
TABLE_01
Config_ID | ConfigField | ConfigValue
-----------------------------------------
11 | Name | Basic
11 | Version | 1.01
11 | Owner | Jack
12 | Name | Advanced
12 | Version | 1.03
12 | Owner | Andy
TABLE_02
Config_ID | Name | Version | Owner
--------------------------------------------
11 | Basic | 1.01 | Jack
12 | Advanced | 1.03 | Andy
I'm trying this:
TRANSFORM ConfigValue
SELECT Config_ID
FROM TABLE_01
GROUP BY Config_ID
PIVOT ConfigField
but got an error:
"Your query does not include the specified expression 'ACValue' as part of aggregate function."
952
TRANSFORM is optional but when included is the first statement in an SQL string. It precedes a SELECT statement that specifies the fields used as row headings and a GROUP BY clause that specifies row grouping.
You can use the PIVOT and UNPIVOT relational operators to change a table-valued expression into another table. PIVOT rotates a table-valued expression by turning the unique values from one column in the expression into multiple columns in the output.
SQL Server pivot IntroductionSQL PIVOT transposes a table-valued expression from a unique set of values from one column into multiple columns in the output and performs aggregations. SQL UNPIVOT performs the opposite operation of SQL PIVOT by transforming the columns of a table-valued expression into column values.
It looks like you are missing the aggregate function in the TRANSFORM:
TRANSFORM Max(ConfigValue)
SELECT Config_ID
FROM TABLE_01
GROUP BY Config_ID
PIVOT ConfigField
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
