Search
Search

Combine multiple rows in one from same table

Vinvinvinvin Published at Dev
15
Vinvinvinvin

I am not quite sure if this is possible through SQL query alone.

Let's say I have a table with the following data and structure:

ID  |  Item Code  |   Store Name  |  Store Price
1   |     101     |      Red      |    10.00
2   |     101     |      Blue     |     9.75
3   |     102     |      Green    |    11.50
4   |     103     |      Black    |     5.75
5   |     103     |      Yellow   |     4.50
6   |     103     |      Purple   |     6.00

And I want to have a result like this:

ItemCode | Store1Name | Store1Price | Store2Name | Store2Price | Store3Name | Store3Price
  101    |    Red     |   10.00     |    Blue    |    9.75     |            |
  102    |    Green   |   11.50     |            |             |            | 
  103    |    Purple  |    6.00     |    Black   |    5.75     |   Yellow   |    4.50

I am currently trying on using JOINS to solve this problem but still can't get the desired result.

Sample JOIN I created:

SELECT A.ItemCode [ItemCode], A.StoreName [Store1Name], A.StorePrice [Store1Price],
       B.StoreName [Store2Name], B.StorePrice [Store2Price],
       C.StoreName [Store3Name], c.StorePrice [Store3Price]
FROM   tblStorePrice A
         LEFT JOIN tblStorePrice B ON A.ItemCode = B.ItemCode AND A.ID <> B.ID
         LEFT JOIN tblStorePrice C ON A.ItemCode = C.ItemCode AND A.ID <> C.ID

Note:

The table only stores three stores per Item Code (at maximum). Item Code with less than 3 stores should have null values for the result.

Hoping to have positive feedback and response. Thanks in advance, guys! :)

Vinvinvinvin

guys! Thanks for your time and effort. I've come up with this solution using JOIN as what I stated earlier. However, I know this is not the best solution so far but I'll be using this for the meantime.

Using SELF JOIN:

SELECT A.ItemCode [ItemCode], A.StoreName [Store1Name], A.StorePrice [Store1Price],B.StoreName [Store2Name], B.StorePrice [Store2Price],C.StoreName [Store3Name], C.StorePrice [Store3Price] FROM tblStorePrice A LEFT JOIN tblStorePrice B ON A.ItemCode = B.ItemCode AND A.ID <> B.ID LEFT JOIN tblStorePrice C ON B.ItemCode = C.ItemCode AND B.ID <> C.ID AND A.ID <> C.ID

This will give a result like this:enter image description here

It's still not the desired output, but inserting this result into another table (with primary key) and selecting the distinct itemcode will do the trick.

Collected from the Internet

Please contact [email protected] to delete if infringement.

edited at
0

Comments

0 comments
Login to comment

Related

From Dev

Combine multiple rows in one from same table

From Dev

Trying to combine multiple rows from a table into one row

From Dev

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

From Dev

Combine Multiple MySQL Queries on the Same Table Into One

From Dev

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

From Dev

Combine multiple rows into one

From Dev

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

From Dev

Combine multiple rows into one row with same number of columns

From Dev

Laravel: Show multiple rows one by one under same id in a table

From Dev

Combine multiple rows to one row

From Dev

SQL Combine multiple rows into one

From Dev

Combine multiple rows into one column

From Dev

Combine multiple rows into one query

From Dev

Combine multiple rows in a MySQL table

From Dev

Pulling multiple rows from the same table from a JOIN table in MySQL

From Dev

combine 2 queries from the same table into one linq query

From Dev

Combine multiple internet sources from same city in one

From Dev

How to combine multiple rows into one rows in SQL

From Dev

Combine colums from table return multiple times the same record

From Dev

Update multiple columns with data retrieved from multiple rows of same table

From Dev

Sqlalchemy single query for multiple rows from one column in one table

From Dev

Calculating the value of multiple rows in a column from one table into an other table

From Dev

Calculating the value of multiple rows in a column from one table into an other table

From Dev

join single value from one table to multiple rows table - Oracle

From Dev

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

From Dev

combine rows of table into one row in crystal reports

From Dev

Combine multiple table results into one table in php

From Dev

How to grab multiple rows from a table with the same value for a foreign key

From Dev

MySQL select and update multiple rows from same table

Related Related

  1. 1

    Combine multiple rows in one from same table

  2. 2

    Trying to combine multiple rows from a table into one row

  3. 3

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

  4. 4

    Combine Multiple MySQL Queries on the Same Table Into One

  5. 5

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

  6. 6

    Combine multiple rows into one

  7. 7

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

  8. 8

    Combine multiple rows into one row with same number of columns

  9. 9

    Laravel: Show multiple rows one by one under same id in a table

  10. 10

    Combine multiple rows to one row

  11. 11

    SQL Combine multiple rows into one

  12. 12

    Combine multiple rows into one column

  13. 13

    Combine multiple rows into one query

  14. 14

    Combine multiple rows in a MySQL table

  15. 15

    Pulling multiple rows from the same table from a JOIN table in MySQL

  16. 16

    combine 2 queries from the same table into one linq query

  17. 17

    Combine multiple internet sources from same city in one

  18. 18

    How to combine multiple rows into one rows in SQL

  19. 19

    Combine colums from table return multiple times the same record

  20. 20

    Update multiple columns with data retrieved from multiple rows of same table

  21. 21

    Sqlalchemy single query for multiple rows from one column in one table

  22. 22

    Calculating the value of multiple rows in a column from one table into an other table

  23. 23

    Calculating the value of multiple rows in a column from one table into an other table

  24. 24

    join single value from one table to multiple rows table - Oracle

  25. 25

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

  26. 26

    combine rows of table into one row in crystal reports

  27. 27

    Combine multiple table results into one table in php

  28. 28

    How to grab multiple rows from a table with the same value for a foreign key

  29. 29

    MySQL select and update multiple rows from same table

HotTag

Archive