Menu
I have a table like this in my database (SQL Server 2008)
ID Type Desc -------------------------------- C-0 Assets No damage C-0 Environment No impact C-0 People No injury or health effect C-0 Reputation No impact C-1 Assets Slight damage C-1 Environment Slight environmental damage C-1 People First Aid Case (FAC) C-1 Reputation Slight impact; Compaints from local community i have to display the Assets, People, Environment and Reputation as columns and display matched Desc as values. But when i run the pivot query, all my values are null.
Can somebody look into my query ans tell me where i am doing wrong?
Select severity_id,pt.[1] As People, [2] as Assets , [3] as Env, [4] as Rep FROM ( select * from COMM.Consequence ) As Temp PIVOT ( max([DESCRIPTION]) FOR [TYPE] In([1], [2], [3], [4]) ) As pt Here is my output
ID People Assets Env Rep ----------------------------------- C-0 NULL NULL NULL NULL C-1 NULL NULL NULL NULL C-2 NULL NULL NULL NULL C-3 NULL NULL NULL NULL C-4 NULL NULL NULL NULL C-5 NULL NULL NULL NULL 
409
You gotta change the name of columns for next Pivot Statement. You can use aggregate of pv3 to sum and group by the column you need. The key point here is that you create new category values by appending 1 or 2 to the end. Without doing this, the pivot query won't work properly.
SQL Server PIVOT operator rotates a table-valued expression. It turns the unique values in one column into multiple columns in the output and performs aggregations on any remaining column values. First, select a base dataset for pivoting. Third, apply the PIVOT operator.
Creating a SQL Server PIVOT table query with an unknown number of columns is exactly one such case. Using the SELECT query, we’ll find all values stored in the outcome_text column. The following code – @columns += QUOTENAME (TRIM (co.outcome_text)) + ‘,’, shall append column name to the list of all previous column names returned by the query.
The PIVOT part of the query consists of 2 parts. In the first, we’ll define which aggregate function we want to apply. In our case, this is – COUNT (call_duration). In FOR part of the query, we’ll define columns. We literary list all the values we want to have as columns.
The following syntax summarizes how to use the PIVOT operator. The column identifiers in the UNPIVOT clause follow the catalog collation. For SQL Database, the collation is always SQL_Latin1_General_CP1_CI_AS. For SQL Server partially contained databases, the collation is always Latin1_General_100_CI_AS_KS_WS_SC.
Select severity_id, pt.People, Assets, Environment, Reputation FROM ( select * from COMM.Consequence ) As Temp PIVOT ( max([DESCRIPTION]) FOR [TYPE] In([People], [Assets], [Environment], [Reputation]) ) As pt 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
