SQL TOP and Join challenge

Question

I have a problem with TOP and JOIN in SQL.

I have two tables INVENTTABLE and IKMTECHSPECPROD.

INVENTTABLE contains all products that we have.

IKMTECHSPECPROD contains all the technical specifications for the products.

There can be many technical specifications for a single product. Now I want to export all the products and the 12 first technical specs for each product, and I want it listed on one line per product

Example:

ItemID, ItemName, spec1name, spec1value, spec2name, spec2value, spec3name, spec3 value.....

I have tried the SQL query below, but it gives me:

ItemID, ItemName, spec1name, spec2value
ItemID, ItemName, spec2name, spec2value 
ItemID, ItemName, spec3name, spec3value

Query:

SELECT 
    invent.ITEMID, ITEMNAME, 
    [techspec].NAME, [techspec].VALUE
FROM 
    [INVENTTABLE] invent
CROSS APPLY 
    (SELECT TOP 12 
         [IKMTECHSPECPROD].NAME, IKMTECHSPECPROD.VALUE
     FROM [IKMTECHSPECPROD]
     WHERE [IKMTECHSPECPROD].ITEMID = invent.ITEMID) techspec

Anyone know how to solve this?

You're looking something like this? http://stackoverflow.com/questions/12671117/comma-separated-values-with-sql-query — James Z, Jun 26 '15 at 11:12

Deepshikha · Answer 1 · 2015-06-26T11:21:17.407

1

You can write a query as:

 SELECT DISTINCT
    T2.ItemID,
    STUFF
    (
        (
            SELECT TOP 12 ',' + specifications
            FROM @IKMTECHSPECPROD T1
            WHERE T1.ItemID = T2.ItemID
            ORDER BY specifications
            FOR XML PATH('')
        ), 1, 1, ''
    ) AS specs
FROM
    @INVENTTABLE T2

DEMO

edited Jun 26 '15 at 11:21

answered Jun 26 '15 at 11:15

Deepshikha

9,896
2
21
21

score 0 · Answer 2 · answered Jun 26 '15 at 11:13

I think you can use group by

SELECT 
    invent.ITEMID, ITEMNAME, 
    [techspec].NAME, [techspec].VALUE
FROM 
    [INVENTTABLE] invent
CROSS APPLY 
    (SELECT TOP 12 
         [IKMTECHSPECPROD].NAME, IKMTECHSPECPROD.VALUE
     FROM [IKMTECHSPECPROD]
     WHERE [IKMTECHSPECPROD].ITEMID = invent.ITEMID
     group by [IKMTECHSPECPROD].NAME, IKMTECHSPECPROD.VALUE
) techspec

score 0 · Answer 3 · answered Jun 26 '15 at 11:50

If you want separate columns for each 12 spec name and value the this could also do the thing:

IF OBJECT_ID('MyTable','U') IS NOT NULL 
    DROP TABLE MyTable
GO
CREATE TABLE MyTable
(
   ItemID    INT,
   ItemName  VARCHAR(30),
   SpecName  VARCHAR(30),
   SpecValue VARCHAR(30)
)
GO
INSERT INTO MyTable
            (ItemID, ItemName, SpecName, SpecValue)
VALUES      (1, 'Item1', 'Item1Spec1', 'Item1Value1'),
            (1, 'Item1', 'Item1Spec2', 'Item1Value2'),
            (1, 'Item1', 'Item1Spec3', 'Item1Value3'),
            (1, 'Item1', 'Item1Spec4', 'Item1Value4'),
            (2, 'Item2', 'Item2Spec1', 'Item2Value1'),
            (2, 'Item2', 'Item2Spec2', 'Item2Value2'),
            (3, 'Item3', 'Item3Spec1', 'Item3Value1'),
            (3, 'Item3', 'Item3Spec2', 'Item3Value2'),
            (3, 'Item3', 'Item3Spec3', 'Item3Value3'),
            (4, 'Item4', 'Item4Spec1', 'Item4Value1')


SELECT ItemID,ItemName, 
MAX(CASE WHEN ItemSpecNo = 1 THEN SpecName END) AS [spec1name], 
MAX(CASE WHEN ItemSpecNo = 1 THEN SpecValue END) AS [spec1value],
MAX(CASE WHEN ItemSpecNo = 2 THEN SpecName END) AS [spec2name], 
MAX(CASE WHEN ItemSpecNo = 2 THEN SpecValue END) AS [spec2value],
MAX(CASE WHEN ItemSpecNo = 3 THEN SpecName END) AS [spec3name], 
MAX(CASE WHEN ItemSpecNo = 3 THEN SpecValue END) AS [spec3value],
MAX(CASE WHEN ItemSpecNo = 4 THEN SpecName END) AS [spec4name], 
MAX(CASE WHEN ItemSpecNo = 4 THEN SpecValue END) AS [spec4value],
MAX(CASE WHEN ItemSpecNo = 5 THEN SpecName END) AS [spec5name], 
MAX(CASE WHEN ItemSpecNo = 5 THEN SpecValue END) AS [spec5value],
MAX(CASE WHEN ItemSpecNo = 6 THEN SpecName END) AS [spec6name], 
MAX(CASE WHEN ItemSpecNo = 6 THEN SpecValue END) AS [spec6value],
MAX(CASE WHEN ItemSpecNo = 7 THEN SpecName END) AS [spec7name], 
MAX(CASE WHEN ItemSpecNo = 7 THEN SpecValue END) AS [spec7value],
MAX(CASE WHEN ItemSpecNo = 8 THEN SpecName END) AS [spec8name], 
MAX(CASE WHEN ItemSpecNo = 8 THEN SpecValue END) AS [spec8value],
MAX(CASE WHEN ItemSpecNo = 9 THEN SpecName END) AS [spec9name], 
MAX(CASE WHEN ItemSpecNo = 9 THEN SpecValue END) AS [spec9value],
MAX(CASE WHEN ItemSpecNo = 10 THEN SpecName END) AS [spec10name], 
MAX(CASE WHEN ItemSpecNo = 10 THEN SpecValue END) AS [spec10value],
MAX(CASE WHEN ItemSpecNo = 11 THEN SpecName END) AS [spec11name], 
MAX(CASE WHEN ItemSpecNo = 11 THEN SpecValue END) AS [spec11value],
MAX(CASE WHEN ItemSpecNo = 12 THEN SpecName END) AS [spec12name], 
MAX(CASE WHEN ItemSpecNo = 12 THEN SpecValue END) AS [spec12value]
FROM
(
SELECT *,ROW_NUMBER()OVER (PARTITION BY itemID ORDER BY itemID) AS ItemSpecNo FROM MyTable
) t
GROUP  BY ItemID,ItemName

SQL TOP and Join challenge

3 Answers3