January 23, 2019 at 7:41 pm
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.
January 24, 2019 at 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...
January 24, 2019 at 1:12 am
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?
January 24, 2019 at 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.
January 24, 2019 at 12:32 pm
Will there only be 2 values or could you have more than 1 alias?
January 24, 2019 at 7:12 pm
Not able to understand your question.please explain.
January 24, 2019 at 8:46 pm
jkramprakash - Thursday, January 24, 2019 10:01 AMIt 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?
January 25, 2019 at 6:04 am
DDL COMMANDSCREATE 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
January 28, 2019 at 8:12 am
pietlinden - Thursday, January 24, 2019 12:01 AMlike this?CREATE TABLE [dbo].[ProductCodes](
[F_Product] [char](1) NOT NULL,
[F_TextCode] [char](7) NOT NULL,
[F_Phrase] [varchar](5) NOT NULL
);
GOINSERT 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
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply