how to get the columns from joined table in a row instead of adjacent columns

Question

I have two tables PRODUCT and PRODUCTMODEL. They can be joined using PRODUCTMODELID. I want to get an output such that rows from PRODUCTMODEL table are below the rows of PRODUCT table FOR EACH PRODUCT.

The output needs to be in a flat file. I can take care of the flat file stuff. I want some direction to get the joined data in rows instead of columns.

CREATE TABLE [PRODUCT](
    [PRODUCTID] [INT] NOT NULL,
    [PRODUCTMODELID] [INT],
    [NAME] [DBO].[NAME] NOT NULL,
    [PRODUCTNUMBER] [NVARCHAR](25) NOT NULL,
)

CREATE TABLE [PRODUCTMODEL](
    [PRODUCTMODELID] [INT] NOT NULL,
    [NAME] [NVARCHAR](25) NOT NULL
)

INSERT INTO PRODUCTMODEL (PRODUCTMODELID, NAME)
VALUES (11, 'LONG-SLEEVE LOGO JERSEY')

INSERT INTO PRODUCT (PRODUCTID, PRODUCTMODELID, NAME, PRODUCTNUMBER)
VALUES (713, 11, 'LONG-SLEEVE LOGO JERSEY', 'LJ-0192-S')

INSERT INTO PRODUCT (PRODUCTID, PRODUCTMODELID, NAME, PRODUCTNUMBER)
VALUES (714, 11, 'LONG-SLEEVE LOGO JERSEY', 'LJ-0192-M')

INSERT INTO PRODUCT (PRODUCTID, PRODUCTMODELID, NAME, PRODUCTNUMBER)
VALUES (715, 11, 'LONG-SLEEVE LOGO JERSEY', 'LJ-0192-L')

INSERT INTO PRODUCT (PRODUCTID, PRODUCTMODELID, NAME, PRODUCTNUMBER)
VALUES (716, 11, 'LONG-SLEEVE LOGO JERSEY', 'LJ-0192-X')

Expected output is:

|PRODUCT|713|LONG-SLEEVE LOGO JERSEY|LJ-0192-S|
|MODEL|11|Long-Sleeve Logo Jersey|
|PRODUCT|714|LONG-SLEEVE LOGO JERSEY|LJ-0192-M|
|MODEL|11|Long-Sleeve Logo Jersey|
|PRODUCT|715|LONG-SLEEVE LOGO JERSEY|LJ-0192-L|
|MODEL|11|Long-Sleeve Logo Jersey|
|PRODUCT|716|LONG-SLEEVE LOGO JERSEY|LJ-0192-X|
|MODEL|11|Long-Sleeve Logo Jersey|

Do you want this in 1 query? Each row has a different number of columns. Or do want a cursor to generate printed output? — Programnik, Jun 17 '19 at 07:45
Normally, you don't want to apply this kind of formatting/presentation logic in your database queries. Database queries are used to get the correct data. How that data is presented/displayed is typically a task for the client application that is used by your users. — Bart Hofland, Jun 17 '19 at 08:17
Programnik , both cursor and query would do. There will be a procedure that will output the data in the above format into a flat file. — Ora Aff, Jun 17 '19 at 09:12

score 1 · Accepted Answer · edited Jun 17 '19 at 07:51

Try this using cross apply

;WITH CTE AS 
(
SELECT CONCAT('|PRODUCT|',P.PRODUCTID,'|',P.NAME,'|',P.PRODUCTNUMBER,'|') AS Col1,
        CONCAT('|MODEL|',PM.PRODUCTMODELID,'|',PM.NAME+'|') AS COl2       
 FROM [PRODUCT] P
 INNER JOIN [PRODUCTMODEL] PM
    ON PM.PRODUCTMODELID = P.PRODUCTMODELID
 )
 SELECT Result 
 FROM CTE
 CROSS APPLY (VALUES (Col1,'Col1'),(Col2,'Col2')
              )DT (Result,Names)

Result

|PRODUCT|713|LJ-0192-S|
|MODEL|11|LONG-SLEEVE LOGO JERSEY|
|PRODUCT|714|LJ-0192-M|
|MODEL|11|LONG-SLEEVE LOGO JERSEY|
|PRODUCT|715|LJ-0192-L|
|MODEL|11|LONG-SLEEVE LOGO JERSEY|
|PRODUCT|716|LJ-0192-X|
|MODEL|11|LONG-SLEEVE LOGO JERSEY|

score 1 · Answer 2 · edited Jun 17 '19 at 09:23

1

;
WITH temp
AS (SELECT
  'Product' AS Type,
  CAST([PRODUCTID] AS varchar(10)) AS PID,
  [PRODUCTMODELID],
  [PRODUCTNUMBER],
  CAST(productModelid AS varchar(10)) + '_1' AS DOrder
FROM Product

UNION

SELECT
  'Model' AS Type,
  '' AS PID,
  [PRODUCTMODELID],
  '' AS ProductNumber,
  Name,
  CAST(productModelid AS varchar(10)) + '_2' AS DOrder
FROM PRODUCTMODEL)

SELECT
  *
FROM temp
ORDER BY dOrder

edited Jun 17 '19 at 09:23

Saadi

2,211
4
21
50

answered Jun 17 '19 at 07:52

Programnik

1,449
1
9
13

Nice and simple +1, although it could be a bit simpler : you don't need to create a CTE to order an UNION, just set the order by on the last one and it's applied to the entire result set. https://stackoverflow.com/questions/4715820/how-to-order-by-with-union – Marc Guillot Jun 17 '19 at 08:00

score 1 · Answer 3 · answered Jun 17 '19 at 07:54

You can try to use JOIN in a subquery or CTE then use CROSS APPLY value to make UNION ALL

;WITH CTE AS (
    SELECT p.PRODUCTMODELID PRODUCTMODELID,
           p.NAME pName,
           pm.Name pmName,
           PRODUCTID,
           PRODUCTNUMBER
    FROM PRODUCT p JOIN PRODUCTMODEL pm 
    ON pm.PRODUCTMODELID =p.PRODUCTMODELID
)
SELECT v.*
FROM CTE CROSS APPLY (
    VALUES 
    ('PRODUCT',pmName,PRODUCTID,PRODUCTNUMBER),
    ('MODEL',pName,PRODUCTMODELID,'')
) v (col1,col2,col3,col4)

sqlfiddle

how to get the columns from joined table in a row instead of adjacent columns

3 Answers3