Order BY clause in View using Stuff Function.

• jkramprakash

  SSCommitted

  Points: 1870

  More actions

  January 28, 2019 at 8:41 am

  #373645

  I have two views.

  My First View definition

  CREATE VIEW FIRST_VIEW AS
SELECT PT.F_PRODUCT AS F_PRODUCT, PT.F_TEXT_CODE AS F_TEXT_CODE, PHT.F_PHRASE AS F_PHRASE,F_COUNTER COUNTER1 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 PT.F_ALIAS AS F_PRODUCT, PT.F_TEXT_CODE AS F_TEXT_CODE, PHT.F_PHRASE AS F_PHRASE,F_COUNTER COUNTER2 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'

  Above view Gives below Output.

  F_PRODUCT        F_TEXT_CODE    F_PHRASE    COUNTER1

  WVTST]PROD00|5     MANU0001     A           7734163
WVTST]PROD00|5     MANU0002     B           7734162
ABPROD-BLACK      MANU0001     C           7714721

  
  created another one view From above view(FIRST_VIEW) using stuff function.
  CREATE VIEW [dbo].[V1_STUFFED] AS
SELECT V1.F_PRODUCT,
    F_TEXT_CODES = STUFF((SELECT DISTINCT ',' + V2.F_TEXT_CODE FROM FIRST_VIEW V2 WHERE V1.F_PRODUCT = V2.F_PRODUCT FOR XML PATH('')),1,1,''),
    F_PHRASES = STUFF((SELECT DISTINCT ' |par ' + V3.F_PHRASE FROM FIRST_VIEW V3 WHERE V1.F_PRODUCT = V3.F_PRODUCT FOR XML PATH('')),1,5,'')
FROM FIRST_VIEW V1 GROUP BY V1.F_PRODUCT
Above stuff view gives below Output.It combines the F_TEXT_CODES AND F_PHRASES in single row for the Products which contains more then one f_text_codes.

  F_PRODUCT          F_TEXT_CODES      F_PHRASES
WVTST]PROD00|5    MANU0001,MANU0002   A,B
ABPROD-BLACK      MANU0001             C      


  But i Required below output.

  F_PRODUCT           F_TEXT_CODES      F_PHRASES
ABPROD-BLACK       MANU0001             C
WVTST]PROD00|5     MANU0002,MANU0001   B,A

  In the above Required output the F_TEXT_CODES(MANU0002) displays before(MANU0001)Text_Code because MANU002 Contains the Counter1 value(7734162)    and MANU0001 CONTAINS COUNTER(7734163) for the Product,so F_TEXT_CODES AND F_PHRASE displays for the F_PRODUCT based order of Counter 1.

  How can i use order by clause in view using stuff for the above requirement?Please help.
  

• Luis Cazares

  SSC Guru

  Points: 183706

  More actions

  January 28, 2019 at 9:12 am

  #2020290

  jkramprakash - Monday, January 28, 2019 8:41 AM

  I have two views.

  My First View definition

  CREATE VIEW FIRST_VIEW AS
SELECT PT.F_PRODUCT AS F_PRODUCT, PT.F_TEXT_CODE AS F_TEXT_CODE, PHT.F_PHRASE AS F_PHRASE,F_COUNTER COUNTER1 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 PT.F_ALIAS AS F_PRODUCT, PT.F_TEXT_CODE AS F_TEXT_CODE, PHT.F_PHRASE AS F_PHRASE,F_COUNTER COUNTER2 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'

  Above view Gives below Output.

  F_PRODUCT        F_TEXT_CODE    F_PHRASE    COUNTER1

  WVTST]PROD00|5     MANU0001     A           7734163
WVTST]PROD00|5     MANU0002     B           7734162
ABPROD-BLACK      MANU0001     C           7714721

  
  created another one view From above view(FIRST_VIEW) using stuff function.
  CREATE VIEW [dbo].[V1_STUFFED] AS
SELECT V1.F_PRODUCT,
    F_TEXT_CODES = STUFF((SELECT DISTINCT ',' + V2.F_TEXT_CODE FROM FIRST_VIEW V2 WHERE V1.F_PRODUCT = V2.F_PRODUCT FOR XML PATH('')),1,1,''),
    F_PHRASES = STUFF((SELECT DISTINCT ' |par ' + V3.F_PHRASE FROM FIRST_VIEW V3 WHERE V1.F_PRODUCT = V3.F_PRODUCT FOR XML PATH('')),1,5,'')
FROM FIRST_VIEW V1 GROUP BY V1.F_PRODUCT
Above stuff view gives below Output.It combines the F_TEXT_CODES AND F_PHRASES in single row for the Products which contains more then one f_text_codes.

  F_PRODUCT          F_TEXT_CODES      F_PHRASES
WVTST]PROD00|5    MANU0001,MANU0002   A,B
ABPROD-BLACK      MANU0001             C      


  But i Required below output.

  F_PRODUCT           F_TEXT_CODES      F_PHRASES
WVTST]PROD00|5     MANU0002,MANU0001   B,A
ABPROD-BLACK       MANU0001             C

  In the above Required output the F_TEXT_CODES(MANU0002) displays before(MANU0001)Text_Code because MANU002 Contains the Counter1 value(7734162)    and MANU0001 CONTAINS COUNTER(7734163) for the Product,so F_TEXT_CODES AND F_PHRASE displays for the F_PRODUCT based order of Counter 1.

  How can i use order by clause in view using stuff for the above requirement?Please help.
  

  Use the ORDER BY before FOR XML.

  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 28, 2019 at 9:38 am

  #2020300

  I tired  the ORDER BY before FOR XML but it is showing error message.

• Luis Cazares

  SSC Guru

  Points: 183706

  More actions

  January 28, 2019 at 9:45 am

  #2020304

  jkramprakash - Monday, January 28, 2019 9:38 AM

  I tired  the ORDER BY before FOR XML but it is showing error message.

  Are we supposed to guess the error message and the code you used?

  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

• Jonathan AC Roberts

  SSCoach

  Points: 18355

  More actions

  January 28, 2019 at 10:35 am

  #2020312

  Have you tried adding an order by in the stuff:
  F_TEXT_CODES = STUFF((SELECT DISTINCT ',' + V2.F_TEXT_CODE FROM FIRST_VIEW V2
WHERE V1.F_PRODUCT = V2.F_PRODUCT ORDER BY colToOrderBy FOR XML PATH('')),1,1,''),
  If that doesn't work you might have to add a TOP(n):
  F_TEXT_CODES = STUFF((SELECT DISTINCT TOP(10000000) ',' + V2.F_TEXT_CODE FROM FIRST_VIEW V2
WHERE V1.F_PRODUCT = V2.F_PRODUCT ORDER BY colToOrderBy FOR XML PATH('')),1,1,''),

• jkramprakash

  SSCommitted

  Points: 1870

  More actions

  January 29, 2019 at 3:43 am

  #2020376

  SELECT V1.F_PRODUCT,
  F_TEXT_CODES = STUFF((SELECT DISTINCT TOP (1000000)',' + V2.F_TEXT_CODE FROM first_view V2 WHERE V1.F_PRODUCT = V2.F_PRODUCT ORDER BY V2.COUNTER1 FOR XML PATH('')),1,1,''),
  F_PHRASES = STUFF((SELECT DISTINCT TOP(100000) ' |par ' + V3.F_PHRASE FROM first_view V3 WHERE V1.F_PRODUCT = V3.F_PRODUCT ORDER BY V3.COUNTER1 FOR XML PATH('')),1,5,'')
FROM first_view V1 GROUP BY V1.F_PRODUCT

  I tried your both query but it is showing below error message.
  ORDER BY items must appear in the select list if SELECT DISTINCT is specified.
  Msg 145, Level 15, State 1, Line 2
  ORDER BY items must appear in the select list if SELECT DISTINCT is specified.

• Jonathan AC Roberts

  SSCoach

  Points: 18355

  More actions

  January 29, 2019 at 4:06 am

  #2020378

  jkramprakash - Tuesday, January 29, 2019 3:43 AM

  SELECT V1.F_PRODUCT,
  F_TEXT_CODES = STUFF((SELECT DISTINCT TOP (1000000)',' + V2.F_TEXT_CODE FROM first_view V2 WHERE V1.F_PRODUCT = V2.F_PRODUCT ORDER BY V2.COUNTER1 FOR XML PATH('')),1,1,''),
  F_PHRASES = STUFF((SELECT DISTINCT TOP(100000) ' |par ' + V3.F_PHRASE FROM first_view V3 WHERE V1.F_PRODUCT = V3.F_PRODUCT ORDER BY V3.COUNTER1 FOR XML PATH('')),1,5,'')
FROM first_view V1 GROUP BY V1.F_PRODUCT

  I tried your both query but it is showing below error message.
  ORDER BY items must appear in the select list if SELECT DISTINCT is specified.
  Msg 145, Level 15, State 1, Line 2
  ORDER BY items must appear in the select list if SELECT DISTINCT is specified.

  Do you need the distinct in the query? i.e. Is there ever more than one value of F_TEXT_CODE the same for a F_PRODUCT?

• Jonathan AC Roberts

  SSCoach

  Points: 18355

  More actions

  January 29, 2019 at 4:33 am

  #2020381

  This should do it:
  SELECT V1.F_PRODUCT,
       STUFF((SELECT ',' + V2.F_TEXT_CODE
                FROM (SELECT V3.F_PRODUCT,
                             V3.F_TEXT_CODE,
                             MIN(V3.COUNTER1) COUNTER1
                        FROM FIRST_VIEW V3
                       GROUP BY V3.F_PRODUCT,
                                V3.F_TEXT_CODE) V2
               WHERE V1.F_PRODUCT = V2.F_PRODUCT
               ORDER BY V2.COUNTER1
                 FOR XML PATH('')),1,1,'') AS F_TEXT_CODES,
       STUFF((SELECT ',' + V2.F_PHRASE
                FROM (SELECT ' |par ' + V3.F_PRODUCT F_PRODUCT,
                             V3.F_PHRASE,
                             MIN(V3.COUNTER1) COUNTER1
                        FROM FIRST_VIEW V3
                       GROUP BY V3.F_PRODUCT,
                                V3.F_PHRASE) V2
               WHERE V1.F_PRODUCT = V2.F_PRODUCT
               ORDER BY V2.COUNTER1
                 FOR XML PATH('')),1,1,'') AS F_PHRASES
  FROM FIRST_VIEW V1
 GROUP BY V1.F_PRODUCT

• jkramprakash

  SSCommitted

  Points: 1870

  More actions

  January 29, 2019 at 5:20 am

  #2020385

  Thank you.Working fine.