Combining Two Queries into Single Query

• jkramprakash

  SSCommitted

  Points: 1870

  More actions

  January 23, 2019 at 7:41 pm

  #409459

  The view  gives the below output.

  CREATE VIEW V1 AS SELECT
SELECT DISTINCT
   PT.PRODUCT AS PRODUCT, PT.TEXT_CODE AS TEXT_CODE,
   PHT.PHRASE AS F_PHRASE
  FROM
   PRODUCT_TEXT PT
  LEFT JOIN
   PHRASE_LINK PHL ON PT.TEXT_CODE = PHL.TEXT_CODE
  INNER JOIN
   PHRASE_TRANSLATIONS PHT ON PHL.PHRASE_ID = PHT.PHRASE_ID
  WHERE
   PT.DATA_CODE = 'MANU' AND PHT.LANGUAGE = 'EN'

    UNION ALL

    SELECT DISTINCT
   PT.F_ALIAS AS PRODUCT, PT.TEXT_CODE AS TEXT_CODE,
   PHT.PHRASE AS F_PHRASE
  FROM
   PROD_ALIAS_TEXT PT
  LEFT JOIN
   PHRASE_LINK PHL ON PT.TEXT_CODE = PHL.TEXT_CODE
  INNER JOIN
   PHRASE_TRANSLATIONS PHT ON PHL.PHRASE_ID = PHT.PHRASE_ID
  WHERE
   PT.DATA_CODE = 'MANU' AND PHT.LANGUAGE = 'EN'

  Output of above view.
   F_PRODUCT   F_TEXTCODE   F_PHRASE
-----------------------------------------------------
A               MANU001    TEST1  
A               MANU002    TEST2  
B               MANU003    XYZ
C               MANU001    ABC
C               MANU005    DEF

  Below is my Required Output.I display F_TEXTCODE and F_PHRASE values in single row for the products which contains more then one F_TEXTCODE.

  F_PRODUCT   F_TEXTCODE     F_PHRASE 
  -----------------------------------------------------
  A     MANU001,MANU002    TEST1,TEST2  
  B     MANU003                XYZ
  C     MANU001,MANU005    ABC,DEF


  I tried below query to achieve above output using stuff function.

  CREATE VIEW V1 AS SELECT         
 with
  alias1 as(
  SELECT DISTINCT PT.F_PRODUCT AS F_PRODUCT, PT.F_TEXT_CODE AS F_TEXT_CODE, PHT.F_PHRASE AS F_PHRASE FROM T_PROD_TEXT PT
  LEFT JOIN T_PHRASE_LINKAGE PHL
      ON PT.F_TEXT_CODE = PHL.F_TEXT_CODE
  INNER JOIN T_PHRASE_TRANSLATIONS PHT
      ON PHL.F_PHRASE_ID = PHT.F_PHRASE_ID
  WHERE PT.F_DATA_CODE = 'MANU' AND PHT.F_LANGUAGE = 'EN'
  UNION ALL
  SELECT DISTINCT PT.F_ALIAS AS F_PRODUCT, PT.F_TEXT_CODE AS F_TEXT_CODE, PHT.F_PHRASE AS F_PHRASE FROM T_PROD_ALIAS_TEXT PT
  LEFT JOIN T_PHRASE_LINKAGE PHL
      ON PT.F_TEXT_CODE = PHL.F_TEXT_CODE
  INNER JOIN T_PHRASE_TRANSLATIONS PHT
      ON PHL.F_PHRASE_ID = PHT.F_PHRASE_ID
  WHERE PT.F_DATA_CODE = 'MANU' AND PHT.F_LANGUAGE = 'EN'
  )
  SELECT V1.F_PRODUCT as F_PRODUCT,
      F_TEXT_CODES = STUFF((SELECT DISTINCT ',' + V2.F_TEXT_CODE FROM alias1 V2 WHERE V1.F_PRODUCT = V2.F_PRODUCT FOR XML PATH('')),1,1,''),
      F_PHRASES = STUFF((SELECT DISTINCT ' |par ' + V3.F_PHRASE FROM alias1 V3 WHERE V1.F_PRODUCT = V3.F_PRODUCT FOR XML PATH('')),1,5,'')
  FROM alias1 V1 GROUP BY V1.F_PRODUCT

  
  i used two queries to achieve the required output.Is it possible to get the answer in single query. Because second query taking some time.
  In above query the STUFF Function apply to all the rows  but it need to apply only the F_PRODUCT contains more then one F_TEXT_CODE values.Please help.

• pietlinden

  SSC Guru

  Points: 63503

  More actions

  January 24, 2019 at 12:01 am

  #2019906

  like this?
  CREATE TABLE [dbo].[ProductCodes](
    [F_Product] [char](1) NOT NULL,
    [F_TextCode] [char](7) NOT NULL,
    [F_Phrase] [varchar](5) NOT NULL
);
GO

  INSERT INTO ProductCodes VALUES
('A','MANU001','TEST1'),
('A','MANU002','TEST2'),
('B','MANU003','XYZ'),
('C','MANU001','ABC'),
('C','MANU005','DEF');

  Solution:
  SELECT
    F_Product
    ,STRING_AGG(F_TextCode, ', ') AS TextCodeList
    ,STRING_AGG(F_Phrase, ', ') AS PhraseList
FROM ProductCodes
GROUP BY F_Product;
-- The query
SELECT x.F_Product
    , x.TextCodesList
    , y.ProductCodesList
FROM
    (SELECT
        C.F_Product,
        STUFF((
            SELECT ', ' + d.F_TextCode
            FROM
                ProductCodes d
            WHERE
                d.F_Product = C.F_Product
            FOR XML PATH('')), 1, 1, '') AS TextCodesList
    FROM
        ProductCodes C
    GROUP BY c.F_Product ) x
INNER JOIN
    (SELECT
        C.F_Product,
        STUFF((
            SELECT ', ' + d.F_Phrase
            FROM
                ProductCodes d
            WHERE
                d.F_Product = C.F_Product
            FOR XML PATH('')), 1, 1, '') AS ProductCodesList
    FROM
        ProductCodes C
    GROUP BY c.F_Product) y
ON x.F_Product = y.F_Product;

  
  I did the two STUFFs separately and then inner joined the results and then queried that...

• jkramprakash

  SSCommitted

  Points: 1870

  More actions

  January 24, 2019 at 1:12 am

  #2019910

  i am using sql server 2014 so it is showing 'STRING_AGG' is not a recognized built-in function name.
  Any other method or way to achieve this?

• jkramprakash

  SSCommitted

  Points: 1870

  More actions

  January 24, 2019 at 10:01 am

  #2019998

  It is working fine.But it is also required two queries.Instead of ProductCodes table i am using the view(v1) and using your query i am taking records from that view(V1).so the query is taking some time for giving output.

• Luis Cazares

  SSC Guru

  Points: 183706

  More actions

  January 24, 2019 at 12:32 pm

  #2020023

  Will there only be 2 values or could you have more than 1 alias?

  Luis C.
  General Disclaimer:
  Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

  How to post data/code on a forum to get the best help: Option 1 / Option 2

• jkramprakash

  SSCommitted

  Points: 1870

  More actions

  January 24, 2019 at 7:12 pm

  #2020056

  Not able to understand your question.please explain.

• pietlinden

  SSC Guru

  Points: 63503

  More actions

  January 24, 2019 at 8:46 pm

  #2020063

  jkramprakash - Thursday, January 24, 2019 10:01 AM

  It is working fine.But it is also required two queries.Instead of ProductCodes table i am using the view(v1) and using your query i am taking records from that view(V1).so the query is taking some time for giving output.

  "It" means what exactly?

  The DISTINCTs in there are pretty brutal. Any chance you can rewrite that query so you don't need it? Can you post the CREATE TABLE and INSERT scripts for the tables the View is based on? And we need the index definitions, too.

• jkramprakash

  SSCommitted

  Points: 1870

  More actions

  January 25, 2019 at 6:04 am

  #2020092

  DDL COMMANDS
  CREATE TABLE [dbo].[PRODUCT_TEXT]
(
[Product] [nvarchar](50) NOT NULL,
[Format] [nvarchar](3) NOT NULL,
[Text_Code] [nvarchar](8) NOT NULL,
[F_Date_Stamp] [datetime] NULL,
[DATA_CODE] [nvarchar](8) NULL,
CONSTRAINT [PK_Staging_T_PROD_TEXT] PRIMARY KEY CLUSTERED
(
[Product] ASC,
[Format] ASC,
[Text_Code] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]
) ON [PRIMARY]
GO

  CREATE TABLE [dbo].PHRASE_LINK]
(
[LINK_ID] [int] NOT NULL,
[PHRASE_ID] [int] NOT NULL,
[DATA_CODE] [nvarchar](8) NOT NULL,
[TEXT_CODE] [nvarchar](8) NOT NULL,
CONSTRAINT [PK_Staging_T_PHRASE_LINKAGE] PRIMARY KEY CLUSTERED
(
[LINK_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] TEXTIMAGE_ON [PRIMARY]
GO

  CREATE TABLE [dbo].[PHRASE_TRANSLATIONS](
[F_TRANSLATION_ID] [int] NOT NULL,
[F_PHRASE_ID] [int] NOT NULL,
[F_LANGUAGE] [nvarchar](2) NOT NULL,
[F_PHRASE] [nvarchar](max) NULL,

  CONSTRAINT [PK_PHRASE_TRANSLATIONS] PRIMARY KEY CLUSTERED
(
[F_TRANSLATION_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] TEXTIMAGE_ON [PRIMARY]
GO

  CREATE TABLE [dbo].[PROD_ALIAS_TEXT]
(
[ALIAS] [varchar](50) NOT NULL,
[FORMAT] [varchar](3) NOT NULL,
[DATA_CODE] [varchar](8) NULL,
[TEXT_CODE] [varchar](8) NOT NULL,

  CONSTRAINT [PROD_ALIAS_TEXT] PRIMARY KEY CLUSTERED
(
[F_ALIAS] ASC,
[F_FORMAT] ASC,
[F_TEXT_CODE] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 80) ON [PRIMARY]
) ON [PRIMARY]
GO

  Non clustered Index details for the above tables.

  CREATE INDEX IX_PRODUCT_TEXT_1 ON PRODUCT_TEXT(DATA_CODE, TEXT_CODE) INCLUDE (PRODUCT);
CREATE INDEX IX_PROD_TEXT ON PRODUCT_TEXT(TEXT_CODE,PRODUCT)
CREATE INDEX IX_PROD_ALIAS_TEXT_1 ON PROD_ALIAS_TEXT(DATA_CODE, TEXT_CODE) INCLUDE (ALIAS);
CREATE INDEX IX_PHRASE_LINK_1 ON PHRASE_LINK(TEXT_CODE, PHRASE_ID);
CREATE INDEX IX_PHRASE_LINK ON PHRASE_LINK(TEXT_CODE)
CREATE INDEX IX_PHRASE_TRANSLATIONS_1 ON PHRASE_TRANSLATIONS(F_LANGUAGE, F_PHRASE_ID) INCLUDE (F_PHRASE);
CREATE IX_PHRASE_Translations_LANG ON PHRASE_transaltions(F_LANGUAGE)

  Record details

  PRODUCT_TEXT

  F_Product    F_Format     F_Text_Code    F_Date_Stamp
WVTST]PROD00 5 MTR      MANU0001      2018-11-13 04:22:58.177
WVTST]PROD00 5 MTR      MANU0002      2018-10-27 10:22:08.240

  PHRASE_LINK

  F_LINK_ID    F_PHRASE_ID    F_DATA_CODE    F_TEXT_CODE
84244          2              MANU        MANU0001
184169      21281             MANU        MANU0002

  PHRASE_TRANSLATIONS

  F_TRANSLATION_ID    F_PHRASE_ID    F_LANGUAGE    F_PHRASE
537340              2                EN          XY |par 23 ABC. |par AB, NY 12110 |par Telephone: 00000
539519             21281            EN           F028

• jkramprakash

  SSCommitted

  Points: 1870

  More actions

  January 28, 2019 at 8:12 am

  #2020285

  pietlinden - Thursday, January 24, 2019 12:01 AM

  like this?
  CREATE TABLE [dbo].[ProductCodes](
      [F_Product] [char](1) NOT NULL,
      [F_TextCode] [char](7) NOT NULL,
      [F_Phrase] [varchar](5) NOT NULL
  );
  GO

  INSERT INTO ProductCodes VALUES
('A','MANU001','TEST1'),
('A','MANU002','TEST2'),
('B','MANU003','XYZ'),
('C','MANU001','ABC'),
('C','MANU005','DEF');

  Solution:
  SELECT
    F_Product
    ,STRING_AGG(F_TextCode, ', ') AS TextCodeList
    ,STRING_AGG(F_Phrase, ', ') AS PhraseList
FROM ProductCodes
GROUP BY F_Product;
-- The query
SELECT x.F_Product
    , x.TextCodesList
    , y.ProductCodesList
FROM
    (SELECT
        C.F_Product,
        STUFF((
            SELECT ', ' + d.F_TextCode
            FROM
                ProductCodes d
            WHERE
                d.F_Product = C.F_Product
            FOR XML PATH('')), 1, 1, '') AS TextCodesList
    FROM
        ProductCodes C
    GROUP BY c.F_Product ) x
INNER JOIN
    (SELECT
        C.F_Product,
        STUFF((
            SELECT ', ' + d.F_Phrase
            FROM
                ProductCodes d
            WHERE
                d.F_Product = C.F_Product
            FOR XML PATH('')), 1, 1, '') AS ProductCodesList
    FROM
        ProductCodes C
    GROUP BY c.F_Product) y
ON x.F_Product = y.F_Product;

  
  I did the two STUFFs separately and then inner joined the results and then queried that...

  Thank you