SQL table data horizontally using PIVOT

Girish Mahida

My SQL table is


  GUID               Step_ID        Value
----------------------------------------------
ADFE12-ASDER-...      1             10
ADFE12-ASDER-...      2             20
ADFE12-ASDER-...      3             30
ADFE12-ASDER-...      4             160
CD4563-FG567-...      1             20
CD4563-FG567-...      2             80
Q23RT5-GH678...       1             30
Q23RT5-GH678-...      2             80
Q23RT5-GH678-...      3             20

And Expected result should be


GUID                  1        2        3        4
---------------------------------------------------
ADFE12-ASDER-...      10       20       30       160
CD4563-FG567-...      20       80      NULL     NULL
Q23RT5-GH678-...      30       80      20       NULL

Here I need to get the details on the basis of column whose data type is GUID. I tried using PIVOT table but getting an exception because I cannot use an aggregate function on GUID column. Is there any other alternative or approach I can use to get the above desired result.

Rahul Tripathi

Try this:

select [GUID],[1],[2],[3],[4]
from
(
  select [GUID], Step_ID, Value
  from test
) d
pivot
(
  max(Value)
  for Step_ID in ([1],[2],[3],[4])
) piv;

SQL FIDDLE DEMO

Collected from the Internet

Please contact [email protected] to delete if infringement.

edited at2020-10-23

Comments

0 comments

Pivot table data in sql

Solved: Transposing Data Out within SQL Table not using Pivot option

Pivot Table in SQL (using Groupby)

create pivot table using sql

T-SQL Pivot table not using IN and values from another column in the pivot data based on MIN() & MAX

Pivot data using t sql

Convert SQL Query to Table Using Pivot Table

Dynamically Pivot a SQL data table with summary

Pivoting a table of survey data in SQL without PIVOT

SQL query to pivot data in below table

How to pivot SQL table without aggregate data

SQL -PIVOT Table using dynamic value

How to pivot a table in BigQuery using standard SQL?

Create Matrix table using Pivot in Sql server

Transpose SQL table results using Pivot

How to pivot a table using SQL - Unrecognized value

Pivot table data using GROUP BY ROLLUP

Referencing Pivot Table data using VBA

Change Pivot table data source using VBA

Laravel - Sending data to pivot table using a checkbox

SQL create a pivot table without using the Oracle Pivot clause

SQL Server : join table to itself to show data horizontally

Data restructure using PIVOT and UNPIVOT in SQL

Pivot data row to header using SQL in Postgresql

Pivot and Unpivoting Data Using TSQL in SQL Server

Pivot Data Using a SQL Select Query

SQL pivot table1 into table2 without using dynamic SQL pivot or hardcode query

Combining categorical data with numeric data when using pivot_table

Get data horizontally into html table

TOP Ranking

Article

SQL table data horizontally using PIVOT

SQL table data horizontally using PIVOT

Failed to listen on localhost:8000 (reason: Cannot assign requested address)

Loopback Error: connect ECONNREFUSED 127.0.0.1:3306 (MAMP)

How to import an asset in swift using Bundle.main.path() in a react-native native module

pump.io port in URL

Compiler error CS0246 (type or namespace not found) on using Ninject in ASP.NET vNext

BigQuery - concatenate ignoring NULL

ngClass error (Can't bind ngClass since it isn't a known property of div) in Angular 11.0.3

ggplotly no applicable method for 'plotly_build' applied to an object of class "NULL" if statements

Spring Boot JPA PostgreSQL Web App - Internal Authentication Error

How to remove the extra space from right in a webview?

java.lang.NullPointerException: Cannot read the array length because "<local3>" is null

Jquery different data trapped from direct mousedown event and simulation via $(this).trigger('mousedown');

flutter: dropdown item programmatically unselect problem

How to use merge windows unallocated space into Ubuntu using GParted?

Change dd-mm-yyyy date format of dataframe date column to yyyy-mm-dd

Nuget add packages gives access denied errors

Svchost high CPU from Microsoft.BingWeather app errors

Can't pre-populate phone number and message body in SMS link on iPhones when SMS app is not running in the background

12.04.3--- Dconf Editor won't show com>canonical>unity option

Any way to remove trailing whitespace *FOR EDITED* lines in Eclipse [for Java]?

maven-jaxb2-plugin cannot generate classes due to two declarations cause a collision in ObjectFactory class

Any way to remove trailing whitespace FOR EDITED lines in Eclipse [for Java]?