SQL Combine multiple rows from one table into one row with multiple columns

Mally Published at Dev

Mally

If I have data where the date and time are the same or different as below:

ID    Date               LOC
1     2015-12-02 10:05   A
1     2015-12-02 10:05   B2
2     2015-12-02 10:05   D
2     2015-12-02 10:05   A7P 
2     2015-12-02 10:06   AD

Is there any way of showing the following:

ID   DATE1             LOC1  DATE 2            LOC2  DATE 3            LOC3
1    2015-12-02 10:05  A     2015-12-02 10:05  B2
2    2015-12-02 10:05  D     2015-12-02 10:05  A7P   
2    2015-12-02 10:06  AD

So there will be multiple rows where the ID is the same but where the Date and time are different?

I have used the partition example below and this works perfectly for getting data into one row as I originally asked. But is it possible to show multiple rows when the ID is the same but where the date and time are different.

BELOW IS THE ORIGINAL QUESTION THAT HAS BEEN ANSWERED..

I've looked thrrough all of the answers relating to this question but cant find any code that works and a lot of the questions relate to two tables.

I have one table with multiple rows of data and multiple columns containing different data types e.g.

ID    Date               LOC
1     2015-11-05 10:05   A
1     2015-12-02 10:06   B2
2     2015-12-02 10:05   D
2     2015-12-02 10:05   A7P 
2     2015-12-02 10:06   AD

I simply require one row for each ID with all of the data in multiple columns e.g.

ID   DATE1             LOC1  DATE 2            LOC2  DATE 3            LOC3
1    2015-11-05 10:05  A     2015-12-02 10:06  B2
2    2015-12-02 10:05  D     2015-12-02 10:05  A7P   2015-12-02 10:06  AD

These can have duplicated data and one or more rows for the same ID.

I've tried a few pivot/unpivot sql codes but i get an error about different types in the unpivot.

Any help would be greatly appreciated.

Hogan

This is the basic way to this, first make row_number and then do a join

WITH TAB_RN AS
(
  SELECT ID, Date, LOC, ROW_NUMBER() OVER (PARTITION BY ID, Date ORDER BY LOC) AS RN
  FROM YOUR_TABLE
)
SELECT T1.ID, 
       T1.Date AS DATE1, T1.LOC AS LOC1,
       T2.Date AS DATE2, T2.LOC AS LOC2,
       T3.Date AS DATE3, T3.LOC AS LOC3
FROM TAB_RN T1
LEFT JOIN TAB_RN T2 ON T1.ID = T2.ID AND T1.Date = T2.Date AND T2.RN = 2
LEFT JOIN TAB_RN T3 ON T1.ID = T3.ID AND T1.Date = T2.Date AND T3.RN = 3
WHERE T1.RN = 1

If you don't know "how many" there will be then you have to use this as a template to make dynamic sql.

Collected from the Internet

Please contact [email protected] to delete if infringement.

edited at2021-02-23

Comments

0 comments

From Dev

Related Related

Article

SQL Combine multiple rows from one table into one row with multiple columns

SQL Combine multiple rows from one table into one row with multiple columns

SQL Server Combine multiple rows to one row with multiple columns

Trying to combine multiple rows from a table into one row

How to combine multiple rows from 4 tables into one single row in a new table in SQL?

Combine multiple rows in one from same table

Combine multiple rows in one from same table

Combine multiple rows to one row

Combine Multiple Rows into One Row (One Column) when Using SQL

Combine multiple rows into one row to replace null values in columns

Combine multiple rows into one row with same number of columns

SQL Combine multiple rows into one

How to combine multiple rows into one rows in SQL

Combine multiple rows into one row MySQL

Combine multiple MYSQL rows into one row

Combine multiple rows into one

SQL : How to copy one row in a table in new table with multiple columns

SQL Multiple rows/records into one row with 3 columns

sql split one row to multiple rows based on number of columns available

SQL select one row from table 1 joining on multiple rows in table 2

Combine multiple result rows into one in SQL

Combine multiple result rows into one in SQL

How to combine multiple columns into one column in SQL?

move multiple columns to rows and one row as column

Create view from multiple tables, combine values from multiple rows into one row

Combine multiple rows into one row MySQL and split value from one field to two in same Query

sql query select only one row from multiple rows

how to select one row from one table and multiple rows from other table using joins in mysql,

Combine SQL rows into one row

Multiple rows to one row Oracle SQL

combining multiple rows into one row in sql server