Sql query help urgent(edited)

[charygaru]

6 minutes ago, 4Vikram said:

Nee story link pamputha ani two years aindi Inka raledu 

adenti october 30 last year evado paiki lepindu kada chulleda nuvvu ?? 

[4Vikram]

1 minute ago, charygaru said:

adenti october 30 last year evado paiki lepindu kada chulleda nuvvu ?? 

Dantlo needi edi kaise maloom hotha hain? Title chep will find out 

[charygaru]

5 minutes ago, 4Vikram said:

Dantlo needi edi kaise maloom hotha hain? Title chep will find out 

ee sari fakka neeku thread EM chestha eeda theddu lepithe naaku malla sollufuk furaanam antha enduku gaani 

[Thillana]

1 minute ago, charygaru said:

ee sari fakka neeku thread EM chestha eeda theddu lepithe naaku malla sollufuk furaanam antha enduku gaani 

[4Vikram]

1 hour ago, charygaru said:

ee sari fakka neeku thread EM chestha eeda theddu lepithe naaku malla sollufuk furaanam antha enduku gaani 

Pedhaina yendi ee landasura linguistic language

[RajuBhai7]

11 hours ago, gupta24 said:

 

 ph lo chesa indaka epudu correct ga rasta post . Edi Sql Server lo execute cheyali 

 

Table: 

Domain   col1  col2     col3     col4

Ab          S1     Acq        Dev      3

Ab           S3   Acq          Qa        3

Ab           S2   Acq          Qa        3

Ab           S4     Acq          Pro     3

Result: 

Domain         Col2      Dev        Qa          Prod     Col4

 Ab                   Acq         S1       S2,S3       S4           3    

Paina result la ravali plss help.

Here you go Bro

create table #temptab 

(Domain varchar(12),

col1 varchar(10),

col2 varchar(10),

col3 varchar(10),

col4 int)

insert into #temptab values

('Ab','S1','Acq','Dev',3)

,('Ab','S2','Acq','qa',3)

,('Ab','S3','Acq','qa',3)

,('Ab','S4','Acq','Pro',3)

 

;with cte as(

select domain, Col2,col3, Col4

,Col1=stuff((select ','+col1 from #temptab b where a.Col3=b.Col3 for xml path('')),1,1,'') from #temptab a

group by domain, col2, Col4,col3)

select * from cte s

pivot (max(col1) FOR [col3] in (Dev,qa,Pro) )as pvt

[ZeusClan]

/****** Script for SelectTopNRows command from SSMS  ******/
SELECT DISTINCT [DOMAIN]
      --,[COL1]
      ,[COL2]
      --,[COL3]
      ,[COL4]
  FROM [SQLLEARNING].[dbo].[TBX]

IF OBJECT_ID('TEMPDB..#TEMP') IS NOT NULL
DROP TABLE #TEMP
SELECT  [DOMAIN],COL3, 
    SUBSTRING(d.COL1,1, LEN(d.COL1) - 1) List
INTO #TEMP
FROM
    (
        SELECT DISTINCT [DOMAIN],COL3 
        FROM [SQLLEARNING].[dbo].[TBX]
    ) a
    CROSS APPLY
    (
        SELECT COL1 + ', ' 
        FROM [SQLLEARNING].[dbo].[TBX] AS B 
        WHERE A.COL3 = B.COL3
        FOR XML PATH('')
    ) D (COL1) 

;WITH CTE AS 
(
  SELECT DISTINCT [DOMAIN],[COL2],[COL4]
  FROM [SQLLEARNING].[dbo].[TBX]
),
PivotTable AS (
SELECT [DOMAIN],[Dev], [Pro], [Qa]
FROM
(SELECT [DOMAIN],COL3, List
    FROM #TEMP) AS SourceTable
PIVOT
(
MAX(List)
FOR COL3 IN ([Dev], [Pro], [Qa])
) AS PivotTable
) 

SELECT DISTINCT C.[DOMAIN],C.COL2,DEV,Pro,Qa,C.COL4 FROM CTE C 
LEFT JOIN PivotTable P ON C.[DOMAIN]=P.[DOMAIN] 

i HOPE THIS HELPS BRO, FOR ANY SQL OR DATA SCIENCE LET ME i WILL DO MY BEST TO HELP

[alooparata]

16 hours ago, Thillana said:

[Prince_Fan]

9 hours ago, RajuBhai7 said:

Here you go Bro

create table #temptab 

(Domain varchar(12),

col1 varchar(10),

col2 varchar(10),

col3 varchar(10),

col4 int)

insert into #temptab values

('Ab','S1','Acq','Dev',3)

,('Ab','S2','Acq','qa',3)

,('Ab','S3','Acq','qa',3)

,('Ab','S4','Acq','Pro',3)

 

;with cte as(

select domain, Col2,col3, Col4

,Col1=stuff((select ','+col1 from #temptab b where a.Col3=b.Col3 for xml path('')),1,1,'') from #temptab a

group by domain, col2, Col4,col3)

select * from cte s

pivot (max(col1) FOR [col3] in (Dev,qa,Pro) )as pvt

[Prince_Fan]

15 minutes ago, ZeusClan said:

/****** Script for SelectTopNRows command from SSMS  ******/
SELECT DISTINCT [DOMAIN]
      --,[COL1]
      ,[COL2]
      --,[COL3]
      ,[COL4]
  FROM [SQLLEARNING].[dbo].[TBX]

IF OBJECT_ID('TEMPDB..#TEMP') IS NOT NULL
DROP TABLE #TEMP
SELECT  [DOMAIN],COL3, 
    SUBSTRING(d.COL1,1, LEN(d.COL1) - 1) List
INTO #TEMP
FROM
    (
        SELECT DISTINCT [DOMAIN],COL3 
        FROM [SQLLEARNING].[dbo].[TBX]
    ) a
    CROSS APPLY
    (
        SELECT COL1 + ', ' 
        FROM [SQLLEARNING].[dbo].[TBX] AS B 
        WHERE A.COL3 = B.COL3
        FOR XML PATH('')
    ) D (COL1) 

;WITH CTE AS 
(
  SELECT DISTINCT [DOMAIN],[COL2],[COL4]
  FROM [SQLLEARNING].[dbo].[TBX]
),
PivotTable AS (
SELECT [DOMAIN],[Dev], [Pro], [Qa]
FROM
(SELECT [DOMAIN],COL3, List
    FROM #TEMP) AS SourceTable
PIVOT
(
MAX(List)
FOR COL3 IN ([Dev], [Pro], [Qa])
) AS PivotTable
) 

SELECT DISTINCT C.[DOMAIN],C.COL2,DEV,Pro,Qa,C.COL4 FROM CTE C 
LEFT JOIN PivotTable P ON C.[DOMAIN]=P.[DOMAIN] 

i HOPE THIS HELPS BRO, FOR ANY SQL OR DATA SCIENCE LET ME i WILL DO MY BEST TO HELP

very 

[gupta24]

On 2/10/2018 at 1:36 AM, RajuBhai7 said:

Here you go Bro

create table #temptab 

(Domain varchar(12),

col1 varchar(10),

col2 varchar(10),

col3 varchar(10),

col4 int)

insert into #temptab values

('Ab','S1','Acq','Dev',3)

,('Ab','S2','Acq','qa',3)

,('Ab','S3','Acq','qa',3)

,('Ab','S4','Acq','Pro',3)

 

;with cte as(

select domain, Col2,col3, Col4

,Col1=stuff((select ','+col1 from #temptab b where a.Col3=b.Col3 for xml path('')),1,1,'') from #temptab a

group by domain, col2, Col4,col3)

select * from cte s

pivot (max(col1) FOR [col3] in (Dev,qa,Pro) )as pvt

bro edi try chesa partial ga work ayindi koncham edit chestey saripodi inka 

result ela vastundi: 

Domain         Col2                          Dev                                                   Qa                                       Prod                           Col4

 Ab                   Acq         S1,S1,S1,S1,S1,S1,S1       S2,S3,S2,S2,S2,S3,S3,S3,S3                     S4,S4,S4,S4,S4                3 

 

so DEV,QA,PROD lo chala repeat avtunay  

 

 

 

[gupta24]

ltt

[gupta24]

On 2/10/2018 at 11:01 AM, ZeusClan said:

/****** Script for SelectTopNRows command from SSMS  ******/
SELECT DISTINCT [DOMAIN]
      --,[COL1]
      ,[COL2]
      --,[COL3]
      ,[COL4]
  FROM [SQLLEARNING].[dbo].[TBX]

IF OBJECT_ID('TEMPDB..#TEMP') IS NOT NULL
DROP TABLE #TEMP
SELECT  [DOMAIN],COL3, 
    SUBSTRING(d.COL1,1, LEN(d.COL1) - 1) List
INTO #TEMP
FROM
    (
        SELECT DISTINCT [DOMAIN],COL3 
        FROM [SQLLEARNING].[dbo].[TBX]
    ) a
    CROSS APPLY
    (
        SELECT COL1 + ', ' 
        FROM [SQLLEARNING].[dbo].[TBX] AS B 
        WHERE A.COL3 = B.COL3
        FOR XML PATH('')
    ) D (COL1) 

;WITH CTE AS 
(
  SELECT DISTINCT [DOMAIN],[COL2],[COL4]
  FROM [SQLLEARNING].[dbo].[TBX]
),
PivotTable AS (
SELECT [DOMAIN],[Dev], [Pro], [Qa]
FROM
(SELECT [DOMAIN],COL3, List
    FROM #TEMP) AS SourceTable
PIVOT
(
MAX(List)
FOR COL3 IN ([Dev], [Pro], [Qa])
) AS PivotTable
) 

SELECT DISTINCT C.[DOMAIN],C.COL2,DEV,Pro,Qa,C.COL4 FROM CTE C 
LEFT JOIN PivotTable P ON C.[DOMAIN]=P.[DOMAIN] 

i HOPE THIS HELPS BRO, FOR ANY SQL OR DATA SCIENCE LET ME i WILL DO MY BEST TO HELP

bro edi try chesa but same vastundi like 

okate column lo repeated with commas vastunay . only single values seperated by comma ravali.

[RajuBhai7]

7 hours ago, gupta24 said:

bro edi try chesa partial ga work ayindi koncham edit chestey saripodi inka 

result ela vastundi: 

Domain         Col2                          Dev                                                   Qa                                       Prod                           Col4

 Ab                   Acq         S1,S1,S1,S1,S1,S1,S1       S2,S3,S2,S2,S2,S3,S3,S3,S3                     S4,S4,S4,S4,S4                3 

 

so DEV,QA,PROD lo chala repeat avtunay  

 

 

 

Add distinct to the query As below 

;WITH cte

AS (

SELECT domain

,Col2

,col3

,Col4

,Col1 = stuff((

SELECT DISTINCT ',' + col1

FROM #temptab b

WHERE a.Col3 = b.Col3

FOR XML path('')

), 1, 1, '')

FROM #temptab a

GROUP BY domain, col2, Col4, col3

)

SELECT *

FROM cte s

pivot(max(col1) FOR [col3] IN (Dev,qa,Pro)) AS pvt