locked
SQL update - source has a row, destination is a column RRS feed

  • Question

  • Hi all

    was hoping for some assistance if possible using the following insertion data

     

    Create table TableA
    (
    Resource_ID int, Project int, FullDate varchar(25), newpercent int
    )
    Insert into TableA values(10199,12993,'[May 14]',2)
    Insert into TableA values(10199,14693,'[May 14]',4)
    Insert into TableA values(10199,14071,'[May 14]',5)
    Insert into TableA values(10199,15486,'[May 14]',16)
    Insert into TableA values(10199,14228,'[May 14]',23)
    Insert into TableA values(10199,15180,'[May 14]',3)
    Insert into TableA values(10199,15181,'[May 14]',38)
    
    Create TABLE TableB
    (
    StaffID int, ProjectID int, [May 14] int
    )
    
    
    Insert into TableB values(10199,12993,2)
    Insert into TableB values(10199,14693,4)
    Insert into TableB values(10199,14071,5)
    Insert into TableB values(10199,15486,11)
    Insert into TableB values(10199,14228,16)
    Insert into TableB values(10199,15180,23)
    Insert into TableB values(10199,15181,39)

    In table A there is a column called Fulldate, which represents a column in table B called [May 14]

    There are 276 of these columns from [Jan 97] to [Dec 2020] in table B that I need to do this same update for, in column A there is a value for every ResourceID, ProjectID, a column name that needs to be updated and a whole number percentage

    How do I write an update that says:

    Update Table B

    Set B.[May14] = A.NewPercent

    Where the resource ID from B  and the projectID and the fulldate matches the column name from table B

    What I need to do is somehow transform the Fulldate so it goes into the column - there are 1200 of these, like Aug 00, Sep 00 and so on from 1997 to 2020 - 276columns or so

    If anyone has an idea how to do the update i'm keen to learn on this one

    My desired result set is:

    StaffID|ProjectID|May 14|Jun 14|Jul 14|

    10199|12993|2|etc|etc

    10199|14693|4|etc|etc

    10199|14071|5|etc|etc

    10199|15486|16|etc|etc

    10199|14228|23|etc|etc

    10199|15180|3|etc|etc

    10199|15181|38|etc|etc

     PS redesigning the table is not possible, this legacy system is 20yrs old and we cannot move off it

    Regards
    J

     

     

     




    • Edited by James OHara Wednesday, July 16, 2014 8:17 PM
    Wednesday, July 16, 2014 5:41 PM

Answers

  • Check the below script:

    CREATE TABLE [dbo].[TableA](
    	[Resource_ID] [int] NULL,
    	[Project] [int] NULL,
    	[FullDate] [varchar](25) NULL,
    	[newpercent] [int] NULL
    ) ON [PRIMARY]
    
    GO
    INSERT [dbo].[TableA] ([Resource_ID], [Project], [FullDate], [newpercent]) VALUES (10199, 12993, N'MAY', 2)
    GO
    INSERT [dbo].[TableA] ([Resource_ID], [Project], [FullDate], [newpercent]) VALUES (10199, 14693, N'MAY', 4)
    GO
    INSERT [dbo].[TableA] ([Resource_ID], [Project], [FullDate], [newpercent]) VALUES (10199, 14071, N'MAY', 5)
    GO
    INSERT [dbo].[TableA] ([Resource_ID], [Project], [FullDate], [newpercent]) VALUES (10199, 15486, N'MAY', 16)
    GO
    INSERT [dbo].[TableA] ([Resource_ID], [Project], [FullDate], [newpercent]) VALUES (10199, 14228, N'MAY', 23)
    GO
    INSERT [dbo].[TableA] ([Resource_ID], [Project], [FullDate], [newpercent]) VALUES (10199, 15180, N'MAY', 3)
    GO
    INSERT [dbo].[TableA] ([Resource_ID], [Project], [FullDate], [newpercent]) VALUES (10199, 15181, N'MAY', 38)
    GO
    INSERT [dbo].[TableA] ([Resource_ID], [Project], [FullDate], [newpercent]) VALUES (10199, 12993, N'JUN', 1)
    GO
    INSERT [dbo].[TableA] ([Resource_ID], [Project], [FullDate], [newpercent]) VALUES (10199, 14693, N'JUN', 6)
    GO
    INSERT [dbo].[TableA] ([Resource_ID], [Project], [FullDate], [newpercent]) VALUES (10199, 14071, N'JUN', 7)
    GO
    INSERT [dbo].[TableA] ([Resource_ID], [Project], [FullDate], [newpercent]) VALUES (10199, 15486, N'JUN', 9)
    GO
    INSERT [dbo].[TableA] ([Resource_ID], [Project], [FullDate], [newpercent]) VALUES (10199, 14228, N'JUN', 11)
    
    GO
    SET NOCOUNT ON
    GO
    DECLARE  @ForumTable
    AS  TABLE(RowNum SMALLINT NOT NULL,
    	Parameter VARCHAR(10) NOT NULL PRIMARY KEY) 
    
    INSERT INTO @ForumTable 
    SELECT ROW_NUMBER()OVER(ORDER BY FullDate),*
    FROM(SELECT DISTINCT FullDate FROM TableA) A
    
    --SELECT * FROM @OrderYear  
    DECLARE @SQL NVARCHAR(4000)
    DECLARE  @DynamicSQL	AS NVARCHAR(4000) 
    DECLARE  @ColumnNames	AS NVARCHAR(4000), 
    		 @IterationYear	AS NVARCHAR(4000),
    		 @RN			AS SMALLINT
    
    SET @RN = (SELECT MIN(RowNum) FROM   @ForumTable) 
    SET @IterationYear=(SELECT Parameter FROM @ForumTable WHERE RowNum=@RN)
    SET @ColumnNames = '' 
    WHILE (@IterationYear IS NOT NULL) 
      BEGIN 
        SET	@ColumnNames = @ColumnNames + ',' + CAST(@IterationYear AS NVARCHAR(10))
    	SET @RN = (SELECT MIN(RowNum) FROM   @ForumTable WHERE  RowNum > @RN) 
    	SET @IterationYear=(SELECT Parameter FROM @ForumTable WHERE RowNum=@RN)
      END 
    SET @ColumnNames = SUBSTRING(@ColumnNames,2,LEN(@ColumnNames)) 
    
    SET @SQL=('SELECT Resource_ID,Project,'+@ColumnNames+'
    FROM(SELECT * FROM TableA) Up
    PIVOT (MAX(newpercent) FOR FullDate IN ('+@ColumnNames+')) Piv
    ORDER BY 1')
    EXEC (@SQL)


    Chaos isn’t a pit. Chaos is a ladder. Many who try to climb it fail and never get to try again. The fall breaks them. And some are given a chance to climb, but they refuse. They cling to the realm, or the gods, or love. Illusions. Only the ladder is real. The climb is all there is.


    • Edited by HimanshuSharma Thursday, July 17, 2014 9:42 AM
    • Proposed as answer by Elvis Long Monday, July 28, 2014 11:25 AM
    • Marked as answer by Elvis Long Friday, August 1, 2014 1:35 AM
    Thursday, July 17, 2014 9:32 AM

All replies

  • Do you think you can have that many columns with your intention to update them?

    For a regular table, the limit is 1024 column and for each row there are size limits too.

    Wednesday, July 16, 2014 6:28 PM
  • Sorry don't know what I was thinking

    1997 - 2020 makes 23years

    therefore there are 276 columns! my bad - in my example I only pulled one, if I know how to do one, then doing the rest should be a doddle, right?



    • Edited by James OHara Wednesday, July 16, 2014 7:42 PM
    Wednesday, July 16, 2014 7:40 PM
  • Can you provide sample data more than one columns for tableB and the form in tableA?

    Do you really have a tableB in place? what are the data type for these columns? Thanks.

    Wednesday, July 16, 2014 9:07 PM
  • use test1
    ;
    
    
    Create table TableA
    (
    Resource_ID int, Project int, FullDate varchar(25), newpercent smallint )
    Insert into TableA values(10199,12993,'May 14',2)
    Insert into TableA values(10199,14693,'May 14',4)
    Insert into TableA values(10199,14071,'May 14',5)
    Insert into TableA values(10199,15486,'May 14',16)
    Insert into TableA values(10199,14228,'May 14',23)
    Insert into TableA values(10199,15180,'May 14',3)
    Insert into TableA values(10199,15181,'May 14',38)
    Insert into TableA values(10199,15180,'Aug 14',88)
    Insert into TableA values(10199,15181,'AUG 14',99)
    
    
    Create TABLE TableB
    (
    StaffID int, ProjectID int, [May 14] int, [AUG 14] int
    )
    
    
    Insert into TableB values(10199,12993,2,null)
    Insert into TableB values(10199,14693,4,null)
    Insert into TableB values(10199,14071,5,null)
    Insert into TableB values(10199,15486,11,null)
    Insert into TableB values(10199,14228,16,null)
    Insert into TableB values(10199,15180,23,8)
    Insert into TableB values(10199,15181,39,9)
    
    
    ;WITH MYCTE AS 
    (
    select Resource_ID , Project,
     max(case when FullDate='May 14' Then newpercent End) as [May 14],
     max(case when FullDate='Aug 14' Then newpercent End) as [Aug 14]
     FROM tableA
     Group by Resource_ID , Project
    
    --select Resource_ID,Project,  [May 14],[Aug 14] from (select * from tablea ) src 
    --pIVOT (max(newpercent) for FullDate In ([May 14],[Aug 14])) pvt
    
    
    )
    
    --
    --select * from mycte
    
    Merge TableB B
    Using MYCTE A On B.StaffID=A.Resource_ID AND B.ProjectID = A.Project
     when matched Then
     Update
    Set 
    [May 14] =A.[May 14],
    [AUG 14]= A.[AUG 14] ;
    
    
    
    
    
    select * from TableB
    
    
    drop table TableA, TableB

    Wednesday, July 16, 2014 9:49 PM
  • You should really look into getting that monstrosity sorted out.

    Here's the quick way: (I only did a year, because I didn't want to script out a table with 200+ columns).

    DECLARE @myTable TABLE (recordDate DATE, recordMonth INT, recordYear INT, staffID INT, projectID INT, value FLOAT)
    DECLARE @i INT = 0
    
    SET NOCOUNT ON
    
    WHILE @i <= 276
    BEGIN
    INSERT INTO @myTable (recordDate, recordMonth, recordYear, staffID, projectID, value)
    VALUES  
    (DATEADD(MONTH,@i,'1997-01-01'),MONTH(DATEADD(MONTH,@i,'1997-01-01')),YEAR(DATEADD(MONTH,@i,'1997-01-01')), 1, 1, @i*10)
    SET @i = @i + 1
    END
    
    SET NOCOUNT OFF
    
    CREATE TABLE #badBadBadTable (staffID int, projectID int, Jan97 Float, Feb97 Float, Mar97 Float, Apr97 Float, May97 Float, Jun97 Float, Jul97 Float, Aug97 Float, Sep97 Float, Oct97 Float, Nov97 Float, Dec97 Float)
    
    DECLARE @month VARCHAR(10), @recordYear INT, @staffID INT, @projectID INT, @value FLOAT, @dSQL NVARCHAR(MAX) = ''
    
    DECLARE BadFiller CURSOR FOR
    SELECT DATENAME(MONTH,recordDate) AS Month, recordYear, staffID, projectID, value
      FROM @myTable
     WHERE recordDate <= '1997-12-31'
    OPEN BadFiller
    FETCH NEXT FROM BadFiller INTO @Month, @recordYear, @staffID, @projectID, @value
    WHILE @@FETCH_STATUS <> -1
    BEGIN
    
    IF EXISTS (SELECT 'x' FROM #badBadBadTable WHERE staffID = @staffID AND projectID = @projectID)
    BEGIN
    SET @dSQL = 'UPDATE #badBadBadTable set '+ LEFT(@Month,3) + RIGHT(CONVERT(VARCHAR,@recordYear),2) + ' = ' + CONVERT(VARCHAR,@value)
    
    END
    ELSE
    BEGIN
    SET @dSQL = 'insert into #badBadBadTable (staffID, projectID, '+ LEFT(@Month,3) + RIGHT(CONVERT(VARCHAR,@recordYear),2) + ')' +' VALUES ('+CONVERT(VARCHAR,@staffID) + ', '+ CONVERT(VARCHAR,@projectID) +', ' + CONVERT(VARCHAR,@value) + ')'
    END
    EXEC sp_executeSQL @dSQL
    
    FETCH NEXT FROM BadFiller INTO @Month, @recordYear, @staffID, @projectID, @value
    END
    CLOSE BadFiller
    DEALLOCATE BadFiller
    
    SELECT * FROM #badBadBadTable
    
    DROP TABLE #badBadBadTable

    Wednesday, July 16, 2014 9:54 PM
  • UPDATE TableB
    SET    [May 14] = CASE WHEN A.FullDate = '[May 14]' WHEN A.newpercent ELSE [May 14],
           [June 14]= CASE WHEN A.FullDate = '[June 14]' WHEN A.newpercent ELSE [June 14],
           ---
    FROM   TableB B
    JOIN   TableA A ON B.StaffID   = A.Resource_ID
                   AND B.ProjectID = A.Project

    Obviously you don't write this beast by hand, but you write a program to generate it.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Wednesday, July 16, 2014 10:18 PM
  • Check the below script:

    CREATE TABLE [dbo].[TableA](
    	[Resource_ID] [int] NULL,
    	[Project] [int] NULL,
    	[FullDate] [varchar](25) NULL,
    	[newpercent] [int] NULL
    ) ON [PRIMARY]
    
    GO
    INSERT [dbo].[TableA] ([Resource_ID], [Project], [FullDate], [newpercent]) VALUES (10199, 12993, N'MAY', 2)
    GO
    INSERT [dbo].[TableA] ([Resource_ID], [Project], [FullDate], [newpercent]) VALUES (10199, 14693, N'MAY', 4)
    GO
    INSERT [dbo].[TableA] ([Resource_ID], [Project], [FullDate], [newpercent]) VALUES (10199, 14071, N'MAY', 5)
    GO
    INSERT [dbo].[TableA] ([Resource_ID], [Project], [FullDate], [newpercent]) VALUES (10199, 15486, N'MAY', 16)
    GO
    INSERT [dbo].[TableA] ([Resource_ID], [Project], [FullDate], [newpercent]) VALUES (10199, 14228, N'MAY', 23)
    GO
    INSERT [dbo].[TableA] ([Resource_ID], [Project], [FullDate], [newpercent]) VALUES (10199, 15180, N'MAY', 3)
    GO
    INSERT [dbo].[TableA] ([Resource_ID], [Project], [FullDate], [newpercent]) VALUES (10199, 15181, N'MAY', 38)
    GO
    INSERT [dbo].[TableA] ([Resource_ID], [Project], [FullDate], [newpercent]) VALUES (10199, 12993, N'JUN', 1)
    GO
    INSERT [dbo].[TableA] ([Resource_ID], [Project], [FullDate], [newpercent]) VALUES (10199, 14693, N'JUN', 6)
    GO
    INSERT [dbo].[TableA] ([Resource_ID], [Project], [FullDate], [newpercent]) VALUES (10199, 14071, N'JUN', 7)
    GO
    INSERT [dbo].[TableA] ([Resource_ID], [Project], [FullDate], [newpercent]) VALUES (10199, 15486, N'JUN', 9)
    GO
    INSERT [dbo].[TableA] ([Resource_ID], [Project], [FullDate], [newpercent]) VALUES (10199, 14228, N'JUN', 11)
    
    GO
    SET NOCOUNT ON
    GO
    DECLARE  @ForumTable
    AS  TABLE(RowNum SMALLINT NOT NULL,
    	Parameter VARCHAR(10) NOT NULL PRIMARY KEY) 
    
    INSERT INTO @ForumTable 
    SELECT ROW_NUMBER()OVER(ORDER BY FullDate),*
    FROM(SELECT DISTINCT FullDate FROM TableA) A
    
    --SELECT * FROM @OrderYear  
    DECLARE @SQL NVARCHAR(4000)
    DECLARE  @DynamicSQL	AS NVARCHAR(4000) 
    DECLARE  @ColumnNames	AS NVARCHAR(4000), 
    		 @IterationYear	AS NVARCHAR(4000),
    		 @RN			AS SMALLINT
    
    SET @RN = (SELECT MIN(RowNum) FROM   @ForumTable) 
    SET @IterationYear=(SELECT Parameter FROM @ForumTable WHERE RowNum=@RN)
    SET @ColumnNames = '' 
    WHILE (@IterationYear IS NOT NULL) 
      BEGIN 
        SET	@ColumnNames = @ColumnNames + ',' + CAST(@IterationYear AS NVARCHAR(10))
    	SET @RN = (SELECT MIN(RowNum) FROM   @ForumTable WHERE  RowNum > @RN) 
    	SET @IterationYear=(SELECT Parameter FROM @ForumTable WHERE RowNum=@RN)
      END 
    SET @ColumnNames = SUBSTRING(@ColumnNames,2,LEN(@ColumnNames)) 
    
    SET @SQL=('SELECT Resource_ID,Project,'+@ColumnNames+'
    FROM(SELECT * FROM TableA) Up
    PIVOT (MAX(newpercent) FOR FullDate IN ('+@ColumnNames+')) Piv
    ORDER BY 1')
    EXEC (@SQL)


    Chaos isn’t a pit. Chaos is a ladder. Many who try to climb it fail and never get to try again. The fall breaks them. And some are given a chance to climb, but they refuse. They cling to the realm, or the gods, or love. Illusions. Only the ladder is real. The climb is all there is.


    • Edited by HimanshuSharma Thursday, July 17, 2014 9:42 AM
    • Proposed as answer by Elvis Long Monday, July 28, 2014 11:25 AM
    • Marked as answer by Elvis Long Friday, August 1, 2014 1:35 AM
    Thursday, July 17, 2014 9:32 AM
  • Thinking more about this: the best is probably to create a view which is unpivoted, and then an INSTEAD OF trigger on this view. Then you can have this unwieldy CASE expression in one single place. (Well two, in different forms. One in the view definitions and one in the trigger.)


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    • Proposed as answer by HimanshuSharma Friday, August 8, 2014 10:08 AM
    Thursday, July 17, 2014 10:31 PM