Combine multiple rows in one from same table

Question

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! :)

Please do not SHOUT your questions here. Typing in ALL CAPS makes it harder to read and won't help you get an answer any more quickly, and quite frankly it's rather annoying. They put Shift keys on both sides of the keyboard to make them easier to use, because properly cased text is easier to read and understand. Please use them. :-) Thanks. — Ken White, Aug 15 '14 at 02:12
possible duplicate of [SQL Server PIVOT examples?](http://stackoverflow.com/questions/24470/sql-server-pivot-examples) — Ken White, Aug 15 '14 at 02:13

score 2 · Accepted Answer · answered Aug 15 '14 at 06:24

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.

Liam Wheldon · Answer 2 · 2014-08-15T02:15:34.533

1

Try this code

Table creation

CREATE TABLE [dbo].[StorePrices](
    [ID] [int] IDENTITY(1,1) NOT NULL,
    [ItemCode] [int] NULL,
    [StoreName] [varchar](256) NULL,
    [StorePrice] [float] NULL,
 CONSTRAINT [PK_StorePrices] PRIMARY KEY CLUSTERED 
(
    [ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

and then SQL for prices

Select 
    DistinctItems.ItemCode,
    StoreRedPrices.StorePrice 'Red',
    StoreBluePrices.StorePrice 'Blue',
    StoreYellowPrices.StorePrice 'Yellow'
from (select distinct ItemCode from storeprices) DistinctItems
left join storeprices StoreRedPrices on StoreRedPrices.ItemCode = DistinctItems.ItemCode and StoreRedPrices.StoreName = 'Red'
left join storeprices StoreBluePrices on StoreBluePrices.ItemCode = DistinctItems.ItemCode and StoreBluePrices.StoreName = 'Blue'
left join storeprices StoreYellowPrices on StoreYellowPrices.ItemCode = DistinctItems.ItemCode and StoreYellowPrices.StoreName = 'Yellow'

EDIT: XML alternative that i'd use, now that we know the stores are dynamic

select 
    ItemCode,
    (select StoreName, StorePrice from StorePrices StorePrice where ItemCode = Item.ItemCode for xml auto,type) StorePrices
from StorePrices Item
group by Item.ItemCode
for xml auto, root('Items')

I hope that this is of some help to you? If not then you may want to look into pivot queries

Regards

Liam

edited Aug 15 '14 at 02:15

answered Aug 15 '14 at 01:47

Liam Wheldon

725
1
5
19

Hey, Liam. Thanks for your answer. I think my previous sample data is confusing, I already edited it for others. Sorry about that. With your query above, I am sure it has the potential, however in my real case it's too defined/generic when using store names as filter specially if hundreds of stores are in the table. Maybe I should look into pivot queries as what you have mentioned. Thanks! :) – Vinvinvinvin Aug 15 '14 at 02:05
Hi, I've just added an XML alternative as I think it'd keep a much better structure to your code and you could even limit to the top 10 cheapest stores or distance from customer too, if you stored that data... Regards – Liam Wheldon Aug 15 '14 at 02:16
Ugh to your "alternative". Querying data to create unnecessary XML which you then (in a comment) suggest *storing* (which means it becomes out of date when any of the data changes). Use a PIVOT or CTE to retrieve the data and forget the XML just for data retrieval. XML is for data exchange, not "storage" as an alternative to doing it properly. – Ken White Aug 15 '14 at 02:30
I never meant for the data to be stored, just a way of keeping the structure of data that he needs instead of creating a table of columns, in which he might not even know the column headers.. This way would mean he has 1 row per item he return and then any shop selling that item would be listed below it. In our applications we use XML a lot as we find the structure works well for things such as this. e.g. if you return a pivot with 10 stores, how would you handle that in say VB.NEt code row.item("column")? however for the XML, you can simple loop through each item within the returned XML. Liam – Liam Wheldon Aug 15 '14 at 02:58

craM 4D-61-72-63 · Answer 3 · 2014-08-15T05:37:14.283

Could likely be solved easier with PIVOT or CTE but I decided to use a cursor

Schema:

CREATE TABLE tblStorePrice (
  ID INTEGER,
  ItemCode INTEGER,
  StoreName VARCHAR(10),
  StorePrice DECIMAL(10,2)
);

INSERT INTO tblStorePrice VALUES (1,101,'Red',10.00)
                                ,(2,101,'Blue',9.75)
                                ,(3,102,'Green',11.50)
                                ,(4,103,'Black',5.75)
                                ,(5,103,'Yellow',4.5)
                                ,(6,103,'Purple',6.00)

Code:

CREATE TABLE #StoreMatrix (
  ItemCode INTEGER,
  Store1Name Varchar(10),
  Store1Price DECIMAL(10,2),
  Store2Name Varchar(10),
  Store2Price DECIMAL(10,2),
  Store3Name Varchar(10),
  Store3Price DECIMAL(10,2),
)

DECLARE @ItemCode INTEGER,
        @Store1Name Varchar(10),
        @Store1Price DECIMAL(10,2),
        @Store2Name Varchar(10),
        @Store2Price DECIMAL(10,2),
        @Store3Name Varchar(10),
        @Store3Price DECIMAL(10,2)

DECLARE cStoreMatrix Cursor FOR
  SELECT DISTINCT ItemCode
  FROM tblStorePrice

OPEN cStoreMatrix

FETCH NEXT FROM cStoreMAtrix INTO @ItemCode

WHILE @@FETCH_STATUS = 0
BEGIN

  SELECT TOP 1 @Store1Name = StoreName, @Store1Price = StorePrice
  FROM tblStorePrice
  WHERE ItemCode = @ItemCode
  ORDER BY StoreName

SQL 2008:

  ;WITH T AS(
    SELECT ROW_NUMBER() OVER(ORDER BY StoreName) AS row, *
    FROM tblStorePrice
    WHERE ItemCode = @ItemCode)
  SELECT @Store2Name = ISNULL(StoreName,'')
        ,@Store2Price = ISNULL(StorePrice,0) FROM T
  WHERE row IN (2)

  ;WITH T AS(
    SELECT ROW_NUMBER() OVER(ORDER BY StoreName) AS row, *
    FROM tblStorePrice
    WHERE ItemCode = @ItemCode)
  SELECT @Store3Name = ISNULL(StoreName,'')
        ,@Store3Price = ISNULL(StorePrice,0) FROM T
  WHERE row IN (3)

SQL 2012:

  SELECT @Store2Name = ISNULL(StoreName,'')
        ,@Store2Price = ISNULL(StorePrice,0)
  FROM tblStorePrice
  WHERE ItemCode = @ItemCode
  ORDER BY StoreName
  Offset 1 Rows 
  Fetch Next 1 Rows Only

  SELECT @Store3Name = ISNULL(StoreName,'')
        ,@Store3Price = ISNULL(StorePrice,0)
  FROM tblStorePrice
  WHERE ItemCode = @ItemCode
  ORDER BY StoreName
  Offset 2 Rows 
  Fetch Next 1 Rows Only

Code:

  INSERT INTO #storeMatrix( ItemCode
                           ,Store1Name
                           ,Store1Price
                           ,Store2Name
                           ,Store2Price
                           ,Store3Name
                           ,Store3Price)
  VALUES( @ItemCode
         ,@Store1Name
         ,@Store1Price
         ,@Store2Name
         ,@Store2Price
         ,@Store3Name
         ,@Store3Price)

  SELECT @Store1Name = NULL
        ,@Store1Price = NULL
        ,@Store2Name = NULL
        ,@Store2Price = NULL
        ,@Store3Name = NULL
        ,@Store3Price = NULL


FETCH NEXT FROM cStoreMAtrix INTO @ItemCode
END
CLOSE cStoreMatrix
DEALLOCATE CStoreMatrix

SELECT * FROM #StoreMatrix

Combine multiple rows in one from same table

3 Answers3