Modify SQL Query to Add Names

you mean:

select * from
(
  SELECT t.[Timestamp], t.data
         , TL.Name as Value_Code
  FROM TLData t
  JOIN TL 
    ON TL.TLInstance = t.TLInstance
  WHERE t.TLInstance in (@TLInstance1,@TLInstance2,@TLInstance3,@TLInstance4,@TLInstance5,@TLInstance6) and isnumeric(data)=1 and Type=0 and (timestamp>@SDate and Timestamp < @EDate)
) srce
PIVOT
( max(data) for Value_Code in ([A_Value],[B_Value],[C_Value],[D_Value],[E_Value],[F_Value])) pvt

Select allOpen in new window

however, you might want to have the pivot syntax "dynamically" based on the possible values in table TL, in which case you need to build the SQL dynamically...
which is explained here:
http://www.kodyaz.com/articles/t-sql-pivot-tables-in-sql-server-tutorial-with-examples.aspx

I'd still need to existing A_Value,B_Value etc.. with the added A_Name, B_Name for the joined names.

So this needs to stay:

SELECT [Timestamp], data
         , case when tlinstance = @TLInstance1 then 'A_Value'
                when tlinstance = @TLInstance2 then 'B_Value'
                when tlinstance = @TLInstance3 then 'C_Value'
                when tlinstance = @TLInstance4 then 'D_Value'
                when tlinstance = @TLInstance5 then 'E_Value'
                else 'F_Value' end as Value_Code

Sorry, I see where those values are built now, just not the A_Name etc..

you lost me now.
please clarify the values of TL and the relevant output for the PIVOT related to those values.

TL has TLInstance and Name. It is used to get the name of the TLInstance, and join to the Data in TLData.

So, we need to select TL.Name where TL.TLInstance = TLData.TLInstance, so we have columns:

A_Value, A_Name,B_Value, B_Name,C_Value, C_Name,D_Value, D_Name,E_Value, E_Name,F_Value, F_Name, Timestamp

the question is still the same: what do you want to do in the pivot output?
knowing that you cannot have 2 "columns" per "code" ...

at least not with the standard PIVOT statement.
you might need a non-pivot sql syntax.

I want to use the 'FIRST' A_Name as the column title in an SSRS Report, each name is a olumn header in the report. I realise I'll have a long list of the same 'NAME', but I can select First or Top of the A_Name column.

I'm also curious as to the performance implication.

still confused.
please show sample data from the 2 tables, and the output you want to get.

More like...


select * from
(
  SELECT [Timestamp], data, TL.Name as value_code
  FROM TLData
  INNER JOIN TL on TL.TLInstance = TLDATA.TLInstance
  WHERE TLDATA.TLInstance in (@TLInstance1,@TLInstance2,@TLInstance3,@TLInstance4,@TLInstance5,@TLInstance6) and isnumeric(data)=1 and Type=0 and (timestamp>@SDate and Timestamp < @EDate)
) srce
PIVOT
( max(data) for Value_Code in ([name1],[name2],[name3],[name4],[name5],[name6])) pvt           -- this is the tricky bit - either the names are "known" or need dynamic SQL


so.... Need to resolves those names

declare @names varchar (2000)
select @names = isnull(@names+',','') + '[' + tl.name + ']' 
from TL 
where TLInstance in (@TLInstance1,@TLInstance2,@TLInstance3,@TLInstance4,@TLInstance5,@TLInstance6)

exec ('
 select * from
  (
     SELECT [Timestamp], data, TL.Name as value_code
     FROM TLData
     INNER JOIN TL on TL.TLInstance = TLDATA.TLInstance
     WHERE TLDATA.TLInstance in (@TLInstance1,@TLInstance2,@TLInstance3,@TLInstance4,@TLInstance5,@TLInstance6) and isnumeric(data)=1 and Type=0 and (timestamp>@SDate and Timestamp < @EDate)
  ) srce
 PIVOT
 ( max(data) for Value_Code in ('+@names+')) pvt 
')

Select allOpen in new window

Oh.... hangon,

The above will put the actual names as the column names... You want a fixed format of column names...

There is another GREAT article (<ahem> laughing </ahem>) https://www.experts-exchange.com/A_4256.html

Have a read of that, and lemme have some more thoughts. There is a way to do it directly. I will simply dummy up a test before I post again...

Oh, and that (earlier) dynamic example is not quite correct either (need to string the @tlinstance variables) so, please let me know if you want to explore that a bit further.

Thanks Mark, on the ball as ever..

The last query you posted seems to work very well, can you see any issues with using it? What is the advanage with the Dynamic SQL? I sometime find BIDS doesn't like some dynamic SQL.

I've attached sample data, as AngelIII asked for.
Book1.xlsx

The advatnage with dynamic SQL is you allow the data to determine the outcomes.

But you are not after that. You say you want the columns to be predefined as 'A_Value','A_Name' etc etc

So, what we are aiming for is something like :

Timestamp               a_value     a_name      b_value    b_name
2010-05-01 01:15:00     321546      instance1   231345     instance2
2010-05-01 01:30:00     321645      instance1   231543     instance2

Select allOpen in new window

If, however, you wanted something more like :

Timestamp               Instance1   Instance2
2010-05-01 01:15:00     321546      231345    
2010-05-01 01:30:00     321645      231543    

Select allOpen in new window

Then you would need some dynamic SQL because of those variable / unknown (ie data driven) column names.

Nah, your previous code is perfect. The report works very well.

Thanks.

Sorry AngelIII, I was supposed to share some points.

no problem. you got your solution (code), I was just asking for refinements ...