Union many columns in a single table

Question

I have a table that I want to transition from being a Mesh Table to XYZ Plot View so I can import into an Application to create a 3D Plot of the Data. Currently this table has 1 Column for Seq (X), one Column for Date(Y) and about 520 for Data Points (Z). I am building a Union All view like below but was wondering if there is an easier and more efficient way of doing this:

Select   
    seq as x,
    t_stamp as y,
    D_1 as z
From
    dbo.Table_3D_data
Union All
Select  
    seq as x,
    t_stamp as y,
    D_2 as z
From
    dbo.Table_3D_data
Union All   **......**  
Select   
    seq as x,
    t_stamp as y,
    D_520 as z
From
    dbo.Table_3D_data 
Order By 
    seq

This will go all the way down to D_520 any support in building this simple query easier or more efficient would be great

This will work but it take awhile to write out the query and I am still learning to build better queries.

Answer 1

Let's break it down into two components, "easier" and "more efficient".

1. "Easier": Some queries are easier to type, others easier to maintain or understand. Others are more readily executed with low-level permissions.

Taking a dynamic approach will reduce the number of lines of code, but at the cost of reduced comprehensibility and the requirement for greater permissions.

This will execute the same query, in fewer lines, with less repetition:

declare 
    @n int = 1,
    @max int = 520,
    @sql nvarchar(max) = N'',
    @query nvarchar(max) = N'',
    @union nvarchar(max) = N'' + char(13) + N'Union All' + char(13)
while @n <= @max
begin
    set @sql = '
Select   seq as x
    ,t_stamp as y
    ,D_' + cast(@n as nvarchar(max)) + ' as z
From dbo.Table_3D_data
    '
    set @query += @sql + @union
    set @n+=1
end

set @query = trim(substring(@query, 1, len(@query) - len(@union)))
exec(@query)

Whether or not this is the right approach for you will depend on the context (see below).

2. "More efficient": I can't comment too much on this aspect, other than to say that, generally speaking, this dynamic example will execute similarly efficiently to the one originally posted. There will be a small overhead in running the while loop that concatenates the query string, but this is negligible.

Context: If you ultimately want a view, and not another object, then dynamic sql will not be suitable, since variable declarations and calls to exec() are not permitted in the view definition.

If you need a re-usable object, then wrapping the above in a stored procedure may be a good approach, and would prevent potential performance issues if e.g. joining your view to another view at some point in the future (nested views perform notoriously sluggishly).

You have mentioned in the comments "I can only use tables and designs that are given to me via other Programmers or Applications", suggesting you may not have sufficient permissions to create a procedure. This is something to check with your administrator.

Edit: following up on some of the comments above, it looks like UNPIVOT will be more efficient than UNION ALL, as described briefly in this answer, and in more detail in this article. Thanks to @TN for suggesting it.

To quote the article, "UNION ALL will scan the rows once for every subquery, which considerably decreases the efficiency of the query execution".

Using CROSS APPLY...VALUES, as suggested by @siggemannen, will apparently yield equivalent performance to UNPIVOT, although I've not confirmed this.

As with the UNION ALL approach, UNPIVOT and VALUES each require either explicit listing of all columns, or:

• a dynamic approach similar to the above, or
• string concatenation with an aggregate function like STRING_AGG(), or
• making use of TSQL's inbuilt support for xml. See this post for an example of how xml might be used to concatenate strings (which in your case will be column names).

In either case, in saving repetition you will be introducing additional complexity, which may not be desirable.

In summary:

• Can you do it more easily? I would say no.
• Can you do it more efficiently? Yes, it would seem so.