Dynamic query with pivot to table - need to align columns

• jjcassiday

  SSC Rookie

  Points: 42

  More actions

  September 8, 2017 at 2:42 am

  #339051

  I'm fairly new to SQL and am having some issues.  I have a third party data collection system that populates some tables.  I have written a stored procedure that I intend to execute every 5 minutes to take information from the populated tables, transpose the data and insert it into a pre-structured table.  Currently, data shows up in the final, pre-defined table in the wrong column.  Is there a way to align columns without stating col1=col1, col2=col2 in the INSERT INTO command?  I understand that stating the matching columns is the preferred method of handling this however I need this to be dynamic since as I continue developing code I will call this stored procedure passing a table name to populate.  There will be several final tables to populate.  On top of that, as new tags are added to the system, I'm hoping to just assign the tag in the tagtable and create a column in the final table and not change my SP.  Hope all this makes sense.... 

  example of 2 tables:
  TagTable

  TagIndex	FieldName
  0	Pressure Zone 1
  1	Pressure Zone 2
  2	Pressure Zone 3
  3	Pressure Zone 4
  4	Temperature Zone 1
  5	Temperature Zone 2

  FloatTable:

  DateTime	TagIndex	Value	Transferred
  9/7/2017 3:22	0	150	1
  9/7/2017 3:22	1	205	1
  9/7/2017 3:22	2	182	1
  9/7/2017 3:22	3	199	1
  9/7/2017 3:22	4	145	1
  9/7/2017 3:22	5	180	1
  9/7/2017 3:24	0	152	1
  9/7/2017 3:24	1	205	1
  9/7/2017 3:24	2	180	1
  9/7/2017 3:24	3	202	1
  9/7/2017 3:24	4	148	1
  9/7/2017 3:24	5	181	1

  Desired Result:

  LogDate	Pressure Zone 1	Pressure Zone 2	Pressure Zone 3	Pressure Zone 4	Temperature Zone 1	Temperature Zone 2
  9/7/2017 3:22	150	205	182	199	145	180
  9/7/2017 3:24	152	205	180	202	148	181

  Stored Procedure:
  SET QUOTED_IDENTIFIER ON
GO

  ALTER PROCEDURE [dbo].[PUSH_NEW_FTVSE_LOGDATA]

  AS

  BEGIN

  -- FLAG ITEMS BEING TRANSFERED TO PERMANANT TABLE
UPDATE [FLOATTABLE]
SET TRANSFERED = 0
WHERE TRANSFERED IS NULL

  -- GET VALUES FROM FLOATTABLE AND TAGTABLE TO PUSH

  SELECT [FLOATTABLE].[DATEANDTIME] AS LOGDATE, [TAGTABLE].[FIELDNAME] AS FName,[FLOATTABLE].VAL AS FVal
INTO TEMP3
FROM [FLOATTABLE]
INNER JOIN [TAGTABLE] ON [FLOATTABLE].[TAGINDEX] = [TAGTABLE].[TAGINDEX]
where [FLOATTABLE].[TAGINDEX] < 16 AND [FLOATTABLE].[TRANSFERED] = '0'

  -- TRANSPOSE ROWS INTO COLUMNS (PIVOT ON TAGINDEX)

  DECLARE @cols AS NVARCHAR(MAX),
    @query AS NVARCHAR(MAX);

  SET @cols = STUFF((SELECT distinct ',' + QUOTENAME(c.FName)
    FROM TEMP3 c
    FOR XML PATH(''), TYPE
    ).value('.', 'NVARCHAR(MAX)')
    ,1,1,'')

  set @query = 'SELECT logdate, ' + @cols + ' FROM
    (
        SELECT logdate
            , FVal
            , FName
        FROM Temp3
    ) x
    pivot
    (
        max(FVal)
        for FName in (' + @cols + ')
        ) p '

  -- INSERT TRANSPOSED TABLE INTO PERMANENT TABLE

  INSERT INTO [DBO].[tblACSequip]

  execute(@query)
drop table TEMP3

  UPDATE [FLOATTABLE]
SET TRANSFERED = 1
WHERE TRANSFERED = 0

  END