CODEWITHSUNDEEP
sqlsql-servercase-when
Chris H
Chris H

Reputation: 271

How to change records in table to name a column in SQL?

In SQL I have a table with 3 column, include of : 

ID      NLV         SGLV
m0001   27/03/2017  8
m0001   28/03/2017  3.5
m0002   28/03/2017  8
m0003   26/03/2017  12
M0001   27/03/2017  2

Now, I want to write an SQL query to create a new table have format follow:

ID  SUM(SGLV)   26 (26/03/2017) 27 (27/03/2017) 28 (28/03/2017)
M0001   13.5    0               10              3.5
m0002   8       0               0               8
m0003   12      12              0               0

So, can anybody help me?

Upvotes: 0

Views: 141

Answers (3)

Gaurav Rajput
Gaurav Rajput

Reputation: 647

In SQL Server, below query gives you the required output

DECLARE @SAMPLEDATA TABLE(ID VARCHAR(100),     NLV  DATETIME,       SGLV DECIMAL(18,1))
INSERT INTO @SAMPLEDATA VALUES
('m0001',   '03/27/2017',  8),
('m0001',   '03/28/2017',  3.5),
('m0002',   '03/28/2017',  8),
('m0003',   '03/26/2017',  12),
('M0001',   '03/27/2017',  2)

SELECT ID,ISNULL([03/26/2017],0)[03/26/2017],ISNULL([03/27/2017],0)    [03/27/2017],ISNULL([03/28/2017],0)[03/28/2017] FROM
(
    SELECT ID,NLV,SGLV FROM @SAMPLEDATA
)T
PIVOT
(
    SUM(SGLV) FOR NLV IN([03/26/2017],[03/27/2017],[03/28/2017])
)PIV;

output

------------------------------------------
--ID    03/26/2017  03/27/2017  03/28/2017
------------------------------------------
m0001   0.0         10.0        3.5
m0002   0.0         0.0         8.0
m0003   12.0        0.0         0.0
------------------------------------------

USING DYNAMIC QUERY : As mentioned in comments, if output is required to be generated for current complete month then the same can be achieved using dynamic query also rather than writing CASE for each and every day of month. Try below query:

DECLARE @DYNAMICQUERY NVARCHAR(MAX)=''
DECLARE @DATERANGE VARCHAR(MAX)=''
;WITH COMPLETEMONTH (COL)
AS
(
        SELECT CAST(CAST(MONTH(CURRENT_TIMESTAMP) AS     VARCHAR(2))+'/'+'01/'+CAST(YEAR(CURRENT_TIMESTAMP) AS VARCHAR(4)) AS DATETIME)
            UNION ALL
        SELECT DATEADD(D,1,COL)COL FROM COMPLETEMONTH 
            WHERE COL< DATEADD(D,-1,    (DATEADD(M,1,CAST(CAST(MONTH(CURRENT_TIMESTAMP) AS     VARCHAR(2))+'/'+'01/'+CAST(YEAR(CURRENT_TIMESTAMP) AS VARCHAR(4)) AS     DATETIME))))
)
SELECT @DATERANGE=@DATERANGE+'['+CONVERT(VARCHAR,COL,101)+']'+',' FROM     COMPLETEMONTH
SET @DATERANGE=LEFT(@DATERANGE,LEN(@DATERANGE)-1)

SELECT @DYNAMICQUERY=
'SELECT ID,'+@DATERANGE+' FROM
(
    SELECT ''m0001'' ID ,   ''03/27/2017'' NLV,  8 SGLV
    UNION ALL 
    SELECT ''m0001'',   ''03/28/2017'',  3.5
    UNION ALL 
    SELECT ''m0002'',   ''03/28/2017'',  8
    UNION ALL 
    SELECT ''m0003'',   ''03/26/2017'',  12
    UNION ALL 
    SELECT ''M0001'',   ''03/27/2017'',  2
)T
PIVOT
(
    SUM(SGLV) FOR NLV IN('+@DATERANGE+')
)PIV;'

EXEC SP_EXECUTESQL @DYNAMICQUERY,N'@DATERANGE     VARCHAR(MAX)',@DATERANGE=@DATERANGE

Upvotes: 1

Mahesh.K
Mahesh.K

Reputation: 901

Try this it may help you

select * into #temp from (

select 'm0001'ID,   '27/03/2017' as NLV ,8 SGLV
union all
select 'm0001',   '28/03/2017',  3.5
union all
select 'm0002  ', '28/03/2017' , 8
union all
select 'm0003   ','26/03/2017' ,12
union all
select 'M0001   ','27/03/2017' , 2
) as a


;with cte as (
select ID,isnull([27/03/2017],0)as [27/03/2017],isnull([28/03/2017],0)as [28/03/2017],isnull([26/03/2017],0) as [26/03/2017] from(
select * from #temp
) as a
pivot
(
 max(sglv) for nlv in ([27/03/2017],[28/03/2017],[26/03/2017])
)
as a
)
select id,[27/03/2017]+[28/03/2017]+[26/03/2017] as SUM_SGLV,[27/03/2017],[28/03/2017],[26/03/2017] from cte

Upvotes: 1

Chanukya
Chanukya

Reputation: 5893

    CREATE TABLE #Table1
        ([ID] varchar(5), [NLV] varchar(10), [SGLV] float)
    ;

    INSERT INTO #Table1
        ([ID], [NLV], [SGLV])
    VALUES
        ('m0001', '27/03/2017', 8),
        ('m0001', '28/03/2017', 3.5),
        ('m0002', '28/03/2017', 8),
        ('m0003', '26/03/2017', 12),
        ('M0001', '27/03/2017', 2)
    ;

    SELECT ID,ISNULL([26/03/2017],0)+ISNULL([27/03/2017],0)+ISNULL([28/03/2017],0) AS [SUM(DGLV)],
ISNULL([26/03/2017],0)'26 (26/03/2017)',ISNULL([27/03/2017],0)'27 (27/03/2017)',ISNULL([28/03/2017],0)'28 (28/03/2017)'
FROM 
(
  SELECT *
  FROM #TABLE1
) SRC
PIVOT
(
   SUM(SGLV)  
  FOR NLV IN ([26/03/2017], [27/03/2017],[28/03/2017])
) PIV;

Upvotes: 1

Related Questions