Query Result

• vijay_uitrgpv

  SSC-Addicted

  Points: 405

  More actions

  April 29, 2016 at 12:48 pm

  #316101

  Hi All

  I have following table and its data

  Create table #ServiceDesk (ServiceName varchar(20),Criteria varchar(20))

  insert into #ServiceDesk(ServiceName,Criteria)

  select 'S1','ABC'

  Union all

  select 'S1','ABC'

  Union all

  select 'S1','XYZ'

  Union all

  select 'S1','XYZ'

  Union all

  select 'S1','PQR'

  Union all

  select 'S1','PQR'

  Union all

  select 'S1','PQR'

  Union all

  select 'S3','ABC'

  Union all

  select 'S2','MNO'

  Union all

  select 'S2','MNO'

  Union all

  select 'S2','MNO'

  Union all

  select 'S2','MNO'

  Union all

  select 'S4','MNO'

  I am looking for an attached output.Please find the attachment

• ram302

  SSCommitted

  Points: 1618

  More actions

  April 29, 2016 at 1:00 pm

  #1875693

  Is there something missing here? Is this an assignment? What're the guidelines for output? I'm not seeing a pattern in the output.

• Luis Cazares

  SSC Guru

  Points: 183698

  More actions

  April 29, 2016 at 1:11 pm

  #1875697

  Something like this?

  SELECT DISTINCT

  CASE WHEN MAX(Criteria) OVER( PARTITION BY ServiceName) <> MIN(Criteria) OVER( PARTITION BY ServiceName)

  THEN ServiceName + '-C-' + CAST(DENSE_RANK() OVER( PARTITION BY ServiceName ORDER BY Criteria) AS varchar(3))

  ELSE ServiceName END AS ServiceName,

  Criteria

  FROM #ServiceDesk

  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

• Eirikur Eiriksson

  SSC Guru

  Points: 182882

  More actions

  April 30, 2016 at 1:02 am

  #1875744

  Luis Cazares (4/29/2016)

  ---

  Something like this?

  SELECT DISTINCT

  CASE WHEN MAX(Criteria) OVER( PARTITION BY ServiceName) <> MIN(Criteria) OVER( PARTITION BY ServiceName)

  THEN ServiceName + '-C-' + CAST(DENSE_RANK() OVER( PARTITION BY ServiceName ORDER BY Criteria) AS varchar(3))

  ELSE ServiceName END AS ServiceName,

  Criteria

  FROM #ServiceDesk

  Be careful with the distinct here, can be very costly if the cardinality is high. Better do this in two parts.

  😎

  -- /*

  IF OBJECT_ID(N'tempdb..#ServiceDesk') IS NOT NULL DROP TABLE #ServiceDesk;

  Create table #ServiceDesk

  (

  Service_ID INT IDENTITY(1,1) NOT NULL PRIMARY KEY CLUSTERED

  ,ServiceName varchar(20) NOT NULL

  ,Criteria varchar(20) NOT NULL

  );

  INSERT INTO #ServiceDesk (ServiceName,Criteria)

  VALUES ('S1','ABC')

  ,('S1','ABC')

  ,('S1','XYZ')

  ,('S1','XYZ')

  ,('S1','PQR')

  ,('S1','PQR')

  ,('S1','PQR')

  ,('S3','ABC')

  ,('S2','MNO')

  ,('S2','MNO')

  ,('S2','MNO')

  ,('S2','MNO')

  ,('S4','MNO')

  ;

  CREATE NONCLUSTERED INDEX TMP_NCLIDX_#SERVICEDESK_SERVICENAME_CRITERIA ON #ServiceDesk ( ServiceName ASC , Criteria ASC)

  -- */

  -- 80% of the cost of this query is the distinct sort

  SELECT DISTINCT

  CASE WHEN MAX(Criteria) OVER( PARTITION BY ServiceName) <> MIN(Criteria) OVER( PARTITION BY ServiceName)

  THEN ServiceName + '-C-' + CAST(DENSE_RANK() OVER( PARTITION BY ServiceName ORDER BY Criteria) AS varchar(3))

  ELSE ServiceName END AS ServiceName,

  Criteria

  FROM #ServiceDesk;

  -- No sort operator needed for this query

  ;WITH BASE_DATA AS

  (

  SELECT

  SD.ServiceName

  ,SD.Criteria

  FROM #ServiceDesk SD

  GROUP BY SD.ServiceName

  ,SD.Criteria

  )

  SELECT

  CASE

  WHEN COUNT(BD.ServiceName) OVER

  (

  PARTITION BY BD.ServiceName

  ) = 1 THEN BD.ServiceName

  ELSE

  CONCAT(BD.ServiceName,'-C-',CONVERT(VARCHAR(3),DENSE_RANK() OVER

  (

  PARTITION BY BD.ServiceName

  ORDER BY BD.Criteria

  ),0))

  END AS ServiceName

  ,BD.Criteria

  FROM BASE_DATA BD;