Jump to content

Please Urgent Help!--Ms Sql Meedha

OracleDev

By OracleDev
in Old Discussions

 Share

Recommended Posts

OracleDev Jr Artist

OracleDev

Posted
  • Author
Posted 
CODE	PCS	COND
X986	1234	2
X986	4567	2
X986	4567	4
x987	1234	6
x987	5689	2
x989	5689	4

pina table ni view help tho ila ravali

pcs	x986	x986-cond   	x987  	 x987-cond   	 x989  	x989-cond
1234	Y	 2	         Y	     6      	  N	 0
4567	Y	2,4	         N	     0	          N	0
5689	N	0	          Y	     2      	  Y	4
  • Replies 30
  • Created
  • Last Reply

Top Posters In This Topic

  • OracleDev

    8

  • loveindia

    3

  • ramafdb

    3

  • JollyBoy

    2

Popular Days

Top Posters In This Topic

Popular Days

OracleDev Jr Artist

OracleDev

Posted
  • Author
Posted

aa pcs lo values distinct ga ravali and code lo unna rows distinct ga column ga ravali and column condition ga ravali..pichi ekkipothundhi asalu

OracleDev Jr Artist

OracleDev

Posted
  • Author
Posted

pivot vadanu for column names kani...column names dynamic anta...inka perige chance undhi..and y/n ela rayalo idea ivvandi please

OracleDev Jr Artist

OracleDev

Posted
  • Author
Posted

GCODE DESCRIPTION 1234 4567 5689 X986 ghj 2 4 (null) x987 kut 6 (null) 2 x989 xxx (null) (null) 4

pachimirchi Hero

pachimirchi

Posted
Posted
 
 
DECLARE @t TABLE (code varchar(255),pcs INT,cond VARCHAR(255))
INSERT @t
        ( code, pcs, cond )
VALUES  ( 'x986', -- code - varchar(255)
          1234, -- pcs - int
          '2'  -- cond - int
          )
INSERT @t
        ( code, pcs, cond )
VALUES  ( 'x986', -- code - varchar(255)
          4567, -- pcs - int
          '2'  -- cond - int
          )
INSERT @t
        ( code, pcs, cond )
VALUES  ( 'x986', -- code - varchar(255)
          4567, -- pcs - int
          '4'  -- cond - int
          )
INSERT @t
        ( code, pcs, cond )
VALUES  ( 'x987', -- code - varchar(255)
          1234, -- pcs - int
          '6'  -- cond - int
          )
INSERT @t
        ( code, pcs, cond )
VALUES  ( 'x987', -- code - varchar(255)
          5689, -- pcs - int
          '2'  -- cond - int
          )
INSERT @t
        ( code, pcs, cond )
VALUES  ( 'x989', -- code - varchar(255)
          5689, -- pcs - int
          '4'  -- cond - int
          )
--SELECT * FROM @t
 
 
 
;WITH cte AS
(
SELECT pcs1,CASE WHEN x986 IS NOT NULL THEN 'Y' ELSE 'N' END AS x986,
CASE WHEN x987 IS NOT NULL THEN 'Y' ELSE 'N' END AS x987,
CASE WHEN x989 IS NOT NULL THEN 'Y' ELSE 'N' END AS x989
 
 FROM (SELECT pcs,code,pcs pcs1 FROM @t)t 
PIVOT
(MAX(pcs) FOR code IN(x986,x987,x989))pv 
)
SELECT cte1.pcs,x986,CASE WHEN [x986-cond] IS NULL THEN '0' ELSE [x986-cond] END [x986-cond],
x987,CASE WHEN [x987-cond] IS NULL THEN '0' ELSE [x987-cond] END [x987-cond] ,
x989,
CASE WHEN [x989-cond] IS NULL THEN '0' ELSE [x989-cond] END [x989-cond]
 
 FROM cte
JOIN
(
SELECT DISTINCT pcs,STUFF((SELECT ','+cond FROM @t cte1
where cte1.pcs=cte2.pcs AND cte1.code='x986'
                           
FOR XML PATH('')),1,1,'') "x986-cond"
,
STUFF((SELECT ','+cond FROM @t cte1 
where cte1.pcs=cte2.pcs AND cte1.code='x987'
FOR XML PATH('')),1,1,'')"x987-cond"
 ,
 
 STUFF((SELECT ','+cond FROM @t cte1 
where cte1.pcs=cte2.pcs AND cte1.code='x989'
FOR XML PATH('')),1,1,'')"x989-cond"
 
 FROM @t cte2
 
 
) cte1
ON cte.pcs1=cte1.pcs
 
loveindia LEGEND

loveindia

Posted
Posted

 

 
 
DECLARE @t TABLE (code varchar(255),pcs INT,cond VARCHAR(255))
INSERT @t
        ( code, pcs, cond )
VALUES  ( 'x986', -- code - varchar(255)
          1234, -- pcs - int
          '2'  -- cond - int
          )
INSERT @t
        ( code, pcs, cond )
VALUES  ( 'x986', -- code - varchar(255)
          4567, -- pcs - int
          '2'  -- cond - int
          )
INSERT @t
        ( code, pcs, cond )
VALUES  ( 'x986', -- code - varchar(255)
          4567, -- pcs - int
          '4'  -- cond - int
          )
INSERT @t
        ( code, pcs, cond )
VALUES  ( 'x987', -- code - varchar(255)
          1234, -- pcs - int
          '6'  -- cond - int
          )
INSERT @t
        ( code, pcs, cond )
VALUES  ( 'x987', -- code - varchar(255)
          5689, -- pcs - int
          '2'  -- cond - int
          )
INSERT @t
        ( code, pcs, cond )
VALUES  ( 'x989', -- code - varchar(255)
          5689, -- pcs - int
          '4'  -- cond - int
          )
--SELECT * FROM @t
 
 
 
;WITH cte AS
(
SELECT pcs1,CASE WHEN x986 IS NOT NULL THEN 'Y' ELSE 'N' END AS x986,
CASE WHEN x987 IS NOT NULL THEN 'Y' ELSE 'N' END AS x987,
CASE WHEN x989 IS NOT NULL THEN 'Y' ELSE 'N' END AS x989
 
 FROM (SELECT pcs,code,pcs pcs1 FROM @t)t 
PIVOT
(MAX(pcs) FOR code IN(x986,x987,x989))pv 
)
SELECT cte1.pcs,x986,CASE WHEN [x986-cond] IS NULL THEN '0' ELSE [x986-cond] END [x986-cond],
x987,CASE WHEN [x987-cond] IS NULL THEN '0' ELSE [x987-cond] END [x987-cond] ,
x989,
CASE WHEN [x989-cond] IS NULL THEN '0' ELSE [x989-cond] END [x989-cond]
 
 FROM cte
JOIN
(
SELECT DISTINCT pcs,STUFF((SELECT ','+cond FROM @t cte1
where cte1.pcs=cte2.pcs AND cte1.code='x986'
                           
FOR XML PATH('')),1,1,'') "x986-cond"
,
STUFF((SELECT ','+cond FROM @t cte1 
where cte1.pcs=cte2.pcs AND cte1.code='x987'
FOR XML PATH('')),1,1,'')"x987-cond"
 ,
 
 STUFF((SELECT ','+cond FROM @t cte1 
where cte1.pcs=cte2.pcs AND cte1.code='x989'
FOR XML PATH('')),1,1,'')"x989-cond"
 
 FROM @t cte2
 
 
) cte1
ON cte.pcs1=cte1.pcs

 

 

Well written man, but it is not dynamic if there are many other codes. He wanted it to be dynamic...

loveindia LEGEND

loveindia

Posted
Posted

try below and let me know...

 

wrote most of it... inka konchem undi, I hope you can figure it out from this code... This concept is called Dynamic Pivoting.... Good Luck

 

CREATE TABLE #MyTable
(
Code VARCHAR(4),
Pcs INT,
Cond INT
)
 
INSERT INTO #MyTable(Code, Pcs, Cond ) VALUES ('x986', 1234, 2), ('x986', 4567, 2), ('x986', 4567, 4),
('x987', 1234, 6), ('x987', 5689, 2), ('x989', 5689, 4)
 
DECLARE @ColumnName VARCHAR(MAX)
 
SELECT @ColumnName= ISNULL(@ColumnName + ',','') 
       + QUOTENAME(Code+'-cond')
FROM (SELECT DISTINCT Code FROM #MyTable) AS Cond
 
SELECT @ColumnName
 
DECLARE @ColumnName2 VARCHAR(MAX)
 
SELECT @ColumnName2= ISNULL(@ColumnName2 + ',','') 
       + 'ISNULL('+ QUOTENAME(Code+'-cond') +', 0) AS ' + QUOTENAME(Code+'-cond')
FROM (SELECT DISTINCT Code FROM #MyTable) AS Cond
 
SELECT @ColumnName2
 
DECLARE @DynamicPivotQuery VARCHAR(8000)
 
SET @DynamicPivotQuery = 
  N'SELECT Pcs, ' + @ColumnName2 + '
    INTO ##MyTable2
FROM (SELECT DISTINCT Code+''-cond'' AS Code, Pcs
, STUFF((SELECT '',''+CAST(Cond AS VARCHAR(10))
FROM #MyTable S2 
WHERE S1.Code = S2.Code AND S1.Pcs = S2.Pcs
FOR XML PATH('''')), 1, 1, '''') AS Cond
FROM #MyTable S1) a
    PIVOT(MAX(Cond) 
          FOR Code IN (' + @ColumnName + ')) AS PVTTable'
 
SELECT @DynamicPivotQuery
 
EXEC (@DynamicPivotQuery)
 
SELECT * FROM ##MyTable2
 
DROP TABLE #MyTable
DROP TABLE ##MyTable2
 
loveindia LEGEND

loveindia

Posted
Posted

asalu dynamic concept anti baa

 

Give me a day, I am currently writing an article about it for my website... www.sqlsaga.com

ramafdb Celebrity

ramafdb

Posted
Posted

Give me a day, I am currently writing an article about it for my website... www.sqlsaga.com

kamav20.gif?1368598550

np just telusukiundam ahi adigaa

pachimirchi Hero

pachimirchi

Posted
Posted

Well written man, but it is not dynamic if there are many other codes. He wanted it to be dynamic...

agreed amav20.gif?1368598550

 Share
Go to topic listing
×
×
  • Create New...