This thread popped up in the related section while I was viewing another post. It is an old topic but a good one. one-attribute-per-column
The way to go about doing this is really going to be driven by how the data is going to be used. If the end result is an output for something like SSRS or another application that is expecting a fixed structure, dynamically labeling the columns is not an option. If it is just going to be run as an adhoc query in SSMS then dynamically labeled columns is viable.
Depending on the use case when scripting for an SSRS report I will either leverage the Matrix functionality in SSRS or limit the number of choices the user can make to a fixed amount. While Maxtices are extremely easy to use they make SSRS do a lot of work and can be slow for result sets with a lot data. They are also not good to use if there is some kind of page size constraint as they will continue to expand for as many columns as there are values in the column groups.
So that everyone can see the same data I'm using Transaction Type instead of attributes as mentioned in the original thread, but the principles are basically the same with the primary difference of attributes requiring an extra join or two.
The SQL at the bottom of this post shows the following examples:
Method 1: Matrix, no cap. I like to put a sequence number in the columns to force a sort order in case alphabetically doesn't make any sense.
Method 2: Matrix, Cap. Basically works the same as method 1 only logic in place to limit options
Method 3: Fixed, In this example the user can pick between 1 and 5 transaction types. I prefer this method because it runs faster. I normally do not include the column header names in the result set as they are available locally in the report but included them so that a Pivot table was shown as well. I prefer using if statements over the Pivot statement because of the constraints around using Pivot tables.
Method 4: Dynamic SQL, no cap. This is by far my least favorite option as it is the most complicated to write and debug. it does however offer a tremendous amount of flexibility. the ideal way of doing it would be to write a stored procedure to just run SQL and then insert the raw results into a temp or variable for further manipulation as opposed to writing it all dynamically.
Here are sample results from each of the methods:
Sample Matrix Report:
Declare @TranIdStr varchar(250) = '31,32,33,1' ,@Cap tinyint = 3 Declare @tblCol table( TranTypeID int ,TranType varchar(50) ,SeqNo int ,ColName varchar(30) primary key(tranTypeID)) Declare @tblTran table( TransactionNo int index idxTran clustered ,SequenceNo int ,TranDate Date ,Amount money ,TranTypeID int primary key(transactionNo, Sequenceno)) Insert into @tblCol Select *, 'Col' + cast(SeqNo as varchar(20)) from (Select ID, description , ROW_NUMBER() over (order by Description) as SeqNo from TR_TRANSACTION_TYPE where ID in (Select Element from dbo.FT_SPLIT_LIST(@TranIdStr,','))) as B --normaly this is final step, no insert needed, but I wanted the results for the other Method s insert @tblTran Select top 10000 t.transaction_no , sequence_no , trn_dt , trn_amt , TranTypeID from @tblCol as C inner join T_TRANSACTION as T on c.TranTypeID = t.trn_type order by trn_dt desc Select 'Method 1' as Qtype, T.*, c.TranType , c.SeqNo from @tblTran as T inner join @tblCol as C on t.TranTypeID = c.TranTypeID Select 'Method 2' as Qtype, T.*, c.TranType , c.SeqNo from @tblTran as T inner join (Select * from @tblCol where SeqNo <= @Cap) as C on t.TranTypeID = c.TranTypeID --Method 2 - ideal for reports with a fixed number of colums where the User can choose 1 of X. In this Method the cap is 5 Select 'Method 3' as QType, * from (Select TransactionNo , max(tranDate) as TranDate ,Sum(Amount1) as Amount1 ,Sum(Amount2) as Amount2 ,Sum(Amount3) as Amount3 ,Sum(Amount4) as Amount4 ,Sum(Amount5) as Amount5 from (Select t.TransactionNo , T.TranDate , iif(ColName = 'Col1', Amount, 0.00) as Amount1 , iif(ColName = 'Col2', Amount, 0.00) as Amount2 , iif(ColName = 'Col3', Amount, 0.00) as Amount3 , iif(ColName = 'Col4', Amount, 0.00) as Amount4 , iif(ColName = 'Col5', Amount, 0.00) as Amount5 from @tblTran as T inner join @tblCol as C on t.TranTypeID = c.TranTypeID ) as D group by TransactionNo ) as S cross join(Select isnull(Max(Col1),'')as Hdg1, isnull(max(Col2),'') as Hdg2, isnull(Max(Col3),'') as Hdg3 , isnull(Max(Col4),'') as Hdg4, isnull(Max(Col5),'') as Hdg5 from (Select * from @tblCol pivot (Max(Trantype) FOR ColName IN ([Col1], [Col2], [Col3], [Col4], [Col5]) ) as P) as C) as Col --Method 3: Dynamic SQL Declare @SeqNo tinyint ,@SQL varchar(max) = '' ,@Clm varchar(20) ,@TranTypeId varchar(20) Select @SeqNo = min(SeqNo) from @tblCol While @Seqno is not null Begin Select @TranTypeId = TranTypeID , @Clm = TranType from @tblCol where SeqNo = @SeqNo Set @SQL = @SQL + ', iif(trn_Type = ' + @TranTypeId + ', Trn_Amt, 0.00) as Amount' + Cast(@SeqNo as varchar(20)) + char(13) + Char(10) + ', ''' + replace(@clm,char(39),char(39) + char(39)) + ''' as Hdg' + Cast(@SeqNo as varchar(20)) + ' ' + char(13) + Char(10) --this option can be used if the column name is to be the description, but I would not advise doing so + ', iif(trn_Type = ' + @TranTypeId + ', Trn_Amt, 0.00) as [' + replace(@clm,char(39),char(39) + char(39)) + '] ' + char(13) + Char(10) Select @SeqNo = min(SeqNo) from @tblCol where SeqNo > @SeqNo end Set @SQL = 'Select Top 1000 ''Method 4'' as Qtype, description as tranType, trn_type as tranTypeId, transaction_no , sequence_no , trn_dt as TranDate ' + @SQL + ' from T_TRANSACTION as T inner join TR_TRANSACTION_TYPE as TT on t.trn_type = tt.id where trn_type in (' + @TranIdStr +') order by trn_dt desc' print( @SQL ) Execute(@Sql)
,
Wow! Thanks.
Ashley