Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2012 Forums
 Transact-SQL (2012)
 Best Query For Dynamic Columns

Author  Topic 

adalius
Starting Member

11 Posts
Posted - 2014-11-18 : 15:37:07
I'm looking to find a query that will be a bit more elegant than a previous solution (using VBA to iterate row by row and populate a second table).

To begin with, we have the following tables which I cannot change (they're part of an accounting package):

The first, [PRUnionMN] is empty at the start of each month. Every week payroll is processed and it appends to this table. [co]/[local]/[un] identify the company, union local, and craft of the employee identified by [empl]. It is possible to have the same [empl] in this table more than once under different combinations of the above 3 fields (for instance if an ironworker worked in southern local 8 part of the month and northern local 103 the rest of the month).

[ben_ty<x>] is a ID that corresponds to [PRUnionBenDedCd].[bended_cd] below. [ben_amt<x>] is the amount allocated to the benefit indicated in [ben_ty<x>] total for the month to date. 

CREATE TABLE [dbo].[PRUnionMN](
	[co] [varchar](2) ,
	[local] [varchar](7) ,
	[un] [numeric](12, 0) ,
	[empl] [int] ,
	[ben_ty1] [smallint] ,
	[ben_amt1] [money] ,
	[ben_ty2] [smallint] ,
	[ben_amt2] [money] ,
	[ben_ty3] [smallint] ,
	[ben_amt3] [money] ,
	[ben_ty4] [smallint] ,
	[ben_amt4] [money] ,
	[ben_ty5] [smallint] ,
	[ben_amt5] [money] ,
	[ben_ty6] [smallint] ,
	[ben_amt6] [money] ,
	[ben_ty7] [smallint] ,
	[ben_amt7] [money] ,
	[ben_ty8] [smallint] ,
	[ben_amt8] [money] ,
	[ben_ty9] [smallint] ,
	[ben_amt9] [money] ,
	[ben_ty10] [smallint] ,
	[ben_amt10] [money] ,
	[ben_ty11] [smallint] ,
	[ben_amt11] [money] ,
	[ben_ty12] [smallint] ,
	[ben_amt12] [money] ,
	[ben_ty13] [smallint] ,
	[ben_amt13] [money] ,
	[ben_ty14] [smallint] ,
	[ben_amt14] [money] ,
	[ben_ty15] [smallint] ,
	[ben_amt15] [money] ,
	[ben_ty16] [smallint] ,
	[ben_amt16] [money]
)


This table is a simple key:value pairing of an ID and a description, for instance (12:'Pension') or (3:'Apprentice Training'). 
CREATE TABLE [dbo].[PRUnBenDedCd](
	[bended_cd] [smallint],
	[descr] [varchar](24)
)


This table correlates a company ID [co], union code, and benefit code together with a rate for how much should be deducted hourly.
CREATE TABLE [dbo].[PRUnionBene](
	[co] [varchar](2) ,
	[union_cd] [numeric](12, 0) ,
	[bended_cd] [smallint] ,
        [rate] [float]
)


The question is this, given the names outlined by [PRUnionBenDedCd].[descr] can be modified/added/removed at any time, what is the best way to get a result set that looks like the following:
[co], [un], [local], [empl], [Pension Amount], [Apprentice Training Amount], <so on for all known descriptions>.

If [PRUnionMN].[ben_type<x>] and [ben_amt<x>] were always holding the value of what was allocated to [PRUnionBenDedCd].[descr] for [PRUnionBenDedCd].[bended_cd]=<x>, this would be an easy deal but because of the current layout I'm scratching my head. I've thought about dynamic pivots but I'm not real familiar with PIVOT to begin with, and the data isn't sorted to begin with since [ben_type1] might contain 5 different types of benefits across 5 employees.

Maybe I'm just overthinking this and missing something obvious but I'm puzzled and while I'm familiar with SQL it's not what I deal with day in and day out so I'm not as 'up to snuff' on it as I should be for this project.

I can get more data/info if requested but I think this might be sufficient.

Hopefully someone can point me in the right direction...

adalius
Starting Member

11 Posts
Posted - 2014-11-24 : 10:12:45
Anybody?
Go to Top of Page

gbritton
Master Smack Fu Yak Hacker

2780 Posts
Posted - 2014-11-24 : 11:34:58
How would you calculate Pension Amount and Apprentice Training Amount for one description?
Go to Top of Page

adalius
Starting Member

11 Posts
Posted - 2014-11-24 : 11:47:36
quote:
Originally posted by gbritton

How would you calculate Pension Amount and Apprentice Training Amount for one description?


It isn't calculated, PRUnionMN gives you the values under the ben_ty<x> and ben_amt<x> columns, but the order is non-static. One entry might have ben_ty1 = 12 and the next might have ben_ty3 = 12, so pension isn't always in column ben_ty1 from employee to employee because of how it processes. Again, that's sadly built into the accounting software so I can't change it so that column 1 is always the same thing.


Imagine PRUnBenDed_cd 12 equals 'Pension', you might have a record in PRUnionMN that has (co, local, un, empl, ben_ty1, ben_amt1, ben_ty2, ben_amt2,...) such that ('73', '8', '13', 2553, 3, 40.20, 12, 20.20) which is essentially saying that employee in that union for that company had $40.20 in Apprentice training, and $20.20 in pension that month. Another entry might have ('73', '8', '15', 2202, 12, 50.50, 3, 100.00). As you can see, now pension value is $50.50 but it was stored in a different column (ben_ty2 the first time, ben_ty1 the second).

Does that make sense?
Go to Top of Page

adalius
Starting Member

11 Posts
Posted - 2014-11-24 : 11:53:25
The other idea I was toying with is making a temp table:

CREATE TABLE [BenefitRuns](
	[co] [varchar](2) ,
	[local] [varchar](7) ,
	[un] [numeric](12, 0) ,
	[empl] [int] ,
        [bene_type] [smallint],
        [bene_amt] [money]


And then running 16 separate INSERT calls...

INSERT INTO BenefitRuns (co, local, un, empl, bene_type, bene_amt) SELECT co, local, un, empl, ben_typ1, ben_amt1)

INSERT INTO BenefitRuns (co, local, un, empl, bene_type, bene_amt) SELECT co, local, un, empl, ben_typ2, ben_amt2)

INSERT INTO BenefitRuns (co, local, un, empl, bene_type, bene_amt) SELECT co, local, un, empl, ben_typ3, ben_amt3)
<so on...>


Then at least I'd have a much simpler table with a single benefit per entry grouped by co/local/un/empl combo... just seems like an awful lot of insert calls but it might be faster than the current VBA iteration method...
Go to Top of Page

gbritton
Master Smack Fu Yak Hacker

2780 Posts
Posted - 2014-11-24 : 12:01:32
Can you post some sample data for the three tables and show the desired results from that data?
Go to Top of Page

adalius
Starting Member

11 Posts
Posted - 2014-11-24 : 12:37:33
quote:
Originally posted by gbritton

Can you post some sample data for the three tables and show the desired results from that data?


Hoo boy. I can try. The table schema I posted above was abbreviated to omit fields that aren't pertinent, the actual is much larger.

SQL Server generated a script to create schema and populate the data and that was a 388kb text file. Let me see if I can whittle it down some, unless there's a way to attach the file here or email it?

It might take a bit of time to whittle it down and ensure I'm not referencing omitted data.
Go to Top of Page

gbritton
Master Smack Fu Yak Hacker

2780 Posts
Posted - 2014-11-24 : 13:29:58
Just a few rows with expected output will do
Go to Top of Page

adalius
Starting Member

11 Posts
Posted - 2014-11-24 : 14:19:14
Alright, in all my test data ben_ty8 thru ben_ty16 (and ben_amt8 thru ben_amt16) were all null so I excluded those.

PRUnBenDedCd Table

INSERT [dbo].[PRUnBenDedCd] ([bended_cd], [descr]) VALUES (3, N'HEALTH')
INSERT [dbo].[PRUnBenDedCd] ([bended_cd], [descr]) VALUES (4, N'PENSION')
INSERT [dbo].[PRUnBenDedCd] ([bended_cd], [descr]) VALUES (6, N'APPRENTICE FUND')
INSERT [dbo].[PRUnBenDedCd] ([bended_cd], [descr]) VALUES (7, N'IAP')
INSERT [dbo].[PRUnBenDedCd] ([bended_cd], [descr]) VALUES (9, N'ANNUITY')
INSERT [dbo].[PRUnBenDedCd] ([bended_cd], [descr]) VALUES (13, N'IMPACT')
INSERT [dbo].[PRUnBenDedCd] ([bended_cd], [descr]) VALUES (15, N'CONT ADM FUND')
INSERT [dbo].[PRUnBenDedCd] ([bended_cd], [descr]) VALUES (20, N'MANDATORY TRAINING')


PRUnionBene Table
INSERT [dbo].[PRUnionBene] ([co], [union_cd], [bended_cd], [rate]) VALUES (N'73', 16, 4, 0)
INSERT [dbo].[PRUnionBene] ([co], [union_cd], [bended_cd], [rate]) VALUES (N'73', 6, 2, 0.04)
INSERT [dbo].[PRUnionBene] ([co], [union_cd], [bended_cd], [rate]) VALUES (N'73', 11, 4, 10.02)
INSERT [dbo].[PRUnionBene] ([co], [union_cd], [bended_cd], [rate]) VALUES (N'73', 6, 4, 12.35)
INSERT [dbo].[PRUnionBene] ([co], [union_cd], [bended_cd], [rate]) VALUES (N'73', 2, 2, 1.63)
INSERT [dbo].[PRUnionBene] ([co], [union_cd], [bended_cd], [rate]) VALUES (N'73', 2, 3, 9.10)
INSERT [dbo].[PRUnionBene] ([co], [union_cd], [bended_cd], [rate]) VALUES (N'73', 2, 4, 10.02)
INSERT [dbo].[PRUnionBene] ([co], [union_cd], [bended_cd], [rate]) VALUES (N'73', 4, 2, 1.63)
INSERT [dbo].[PRUnionBene] ([co], [union_cd], [bended_cd], [rate]) VALUES (N'73', 4, 3, 6.80)
INSERT [dbo].[PRUnionBene] ([co], [union_cd], [bended_cd], [rate]) VALUES (N'73', 4, 4, 8.91)


PRUnionMN Table
INSERT [dbo].[PRUnionMN] ([co], [local], [un], [empl], [ben_ty1], [ben_amt1], [ben_ty2], [ben_amt2], [ben_ty3], [ben_amt3], [ben_ty4], [ben_amt4], [ben_ty5], [ben_amt5], [ben_ty6], [ben_amt6], [ben_ty7], [ben_amt7]) VALUES (N'73',N'8',2,7530,3,336.70,4,370.74,6,12.95,9,157.25,13,8.51,7,6.29,20,9.25)
INSERT [dbo].[PRUnionMN] ([co], [local], [un], [empl], [ben_ty1], [ben_amt1], [ben_ty2], [ben_amt2], [ben_ty3], [ben_amt3], [ben_ty4], [ben_amt4], [ben_ty5], [ben_amt5], [ben_ty6], [ben_amt6], [ben_ty7], [ben_amt7]) VALUES (N'73',N'8',2,9038,3,291.20,4,320.64,6,11.20,9,136.00,13,7.36,7,5.44,20,8.00)
INSERT [dbo].[PRUnionMN] ([co], [local], [un], [empl], [ben_ty1], [ben_amt1], [ben_ty2], [ben_amt2], [ben_ty3], [ben_amt3], [ben_ty4], [ben_amt4], [ben_ty5], [ben_amt5], [ben_ty6], [ben_amt6], [ben_ty7], [ben_amt7]) VALUES (N'73',N'8',2,7191,3,1092.00,4,1202.40,6,42.00,9,510.00,13,27.60,7,20.40,20,30.00)
INSERT [dbo].[PRUnionMN] ([co], [local], [un], [empl], [ben_ty1], [ben_amt1], [ben_ty2], [ben_amt2], [ben_ty3], [ben_amt3], [ben_ty4], [ben_amt4], [ben_ty5], [ben_amt5], [ben_ty6], [ben_amt6], [ben_ty7], [ben_amt7]) VALUES (N'73',N'8',2,7262,3,955.50,4,1052.10,6,36.76,9,453.70,13,24.16,7,17.86,20,26.26)
INSERT [dbo].[PRUnionMN] ([co], [local], [un], [empl], [ben_ty1], [ben_amt1], [ben_ty2], [ben_amt2], [ben_ty3], [ben_amt3], [ben_ty4], [ben_amt4], [ben_ty5], [ben_amt5], [ben_ty6], [ben_amt6], [ben_ty7], [ben_amt7]) VALUES (N'73',N'383',4,8395,3,887.40,4,1162.76,6,62.64,9,574.20,15,14.36,13,19.58,0,0.00)
INSERT [dbo].[PRUnionMN] ([co], [local], [un], [empl], [ben_ty1], [ben_amt1], [ben_ty2], [ben_amt2], [ben_ty3], [ben_amt3], [ben_ty4], [ben_amt4], [ben_ty5], [ben_amt5], [ben_ty6], [ben_amt6], [ben_ty7], [ben_amt7]) VALUES (N'73',N'383',4,8446,3,969.00,4,1269.68,6,68.40,9,627.00,15,15.68,13,21.38,0,0.00)
INSERT [dbo].[PRUnionMN] ([co], [local], [un], [empl], [ben_ty1], [ben_amt1], [ben_ty2], [ben_amt2], [ben_ty3], [ben_amt3], [ben_ty4], [ben_amt4], [ben_ty5], [ben_amt5], [ben_ty6], [ben_amt6], [ben_ty7], [ben_amt7]) VALUES (N'73',N'383',4,8448,3,941.80,4,1234.04,6,66.48,9,609.40,15,15.24,13,20.78,0,0.00)
INSERT [dbo].[PRUnionMN] ([co], [local], [un], [empl], [ben_ty1], [ben_amt1], [ben_ty2], [ben_amt2], [ben_ty3], [ben_amt3], [ben_ty4], [ben_amt4], [ben_ty5], [ben_amt5], [ben_ty6], [ben_amt6], [ben_ty7], [ben_amt7]) VALUES (N'73',N'383',4,8462,3,581.40,4,761.82,6,41.04,9,376.20,15,9.42,13,12.84,0,0.00)
INSERT [dbo].[PRUnionMN] ([co], [local], [un], [empl], [ben_ty1], [ben_amt1], [ben_ty2], [ben_amt2], [ben_ty3], [ben_amt3], [ben_ty4], [ben_amt4], [ben_ty5], [ben_amt5], [ben_ty6], [ben_amt6], [ben_ty7], [ben_amt7]) VALUES (N'73',N'383',4,8469,3,877.20,4,1149.39,6,61.92,9,567.60,15,14.19,13,19.35,0,0.00)
INSERT [dbo].[PRUnionMN] ([co], [local], [un], [empl], [ben_ty1], [ben_amt1], [ben_ty2], [ben_amt2], [ben_ty3], [ben_amt3], [ben_ty4], [ben_amt4], [ben_ty5], [ben_amt5], [ben_ty6], [ben_amt6], [ben_ty7], [ben_amt7]) VALUES (N'73',N'383',4,8484,3,768.40,4,1006.84,6,54.24,9,497.20,15,12.44,13,16.96,0,0.00)


Desired Output:
([co], [local], [un], [empl], "HEALTH", "PENSION", "APPRENTICE FUND", "IAP", "ANNUITY", "IMPACT", "CONT ADM FUND", "MANDATORY TRAINING")
I.e.:
'73',	 '8',	  2,	7530,	336.7,	370.74,	        12.95,	      157.25,	8.51,	6.29,		0,		9.25
'73',	 '8',	  2,	9038,	291.2,	320.64,	        11.2,	      136,	7.36,	5.44,		0,		8
'73',	 '8',	  2,	7191,	1092,	1202.4,	        42,	      510,	27.6,	20.4,		0,		30
'73',	 '8',	  2,	7262,	955.5,	1052.1,	        36.76,	      453.7,	24.16,	17.86,		0,		26.26
'73',	'383',	  4,	8395,	887.4,	1162.76,	62.64,	      574.2,	19.58,	0,		14.36,		0
'73',	'383',	  4,	8446,	969,	1269.68,	68.4,	      627,	21.38,	0,		15.68,		0
'73',	'383',	  4,	8448,	941.8,	1234.04,	66.48,	      609.4,	20.78,	0,		15.24,		0
'73',	'383',	  4,	8462,	581.4,	761.82,   	41.04,	      376.2,	12.84,	0,		9.42,		0
'73',	'383',	  4,	8469,	877.2,	1149.39,	61.92,	      567.6,	19.35,	0,		14.19,		0
'73',	'383',	  4,	8484,	768.4,	1006.84,	54.24,	      497.2,	16.96,	0,		12.44,		0
Go to Top of Page

gbritton
Master Smack Fu Yak Hacker

2780 Posts
Posted - 2014-11-24 : 14:45:45
I'm thinking beginning with an unpivot operation (not necessarily the SQL keyword) to produce a derived table from the last table like:


co, local, un, empl, bentype, benamt


as this is probably easier to work with. However for this to work, there needs to be a guarantee that the same benefit type code is in only one of the ben_tyx columns on any given row. Is that the case here?
Go to Top of Page

gbritton
Master Smack Fu Yak Hacker

2780 Posts
Posted - 2014-11-24 : 15:03:09
e.g.


SELECT co, local, un, empl, bene.typ, bene.amt
from dbo.prunionmn mn
CROSS APPLY (
	VALUES (ben_ty1, ben_amt1),
		   (ben_ty2, ben_amt2),
		   (ben_ty3, ben_amt3),
		   (ben_ty4, ben_amt4),
		   (ben_ty5, ben_amt5),
		   (ben_ty6, ben_amt6),
		   (ben_ty7, ben_amt7),
		   (ben_ty8, ben_amt8),
		   (ben_ty9, ben_amt9),
		   (ben_ty10, ben_amt10),
		   (ben_ty11, ben_amt11),
		   (ben_ty12, ben_amt12),
		   (ben_ty13, ben_amt13),
		   (ben_ty14, ben_amt14),
		   (ben_ty15, ben_amt15),
		   (ben_ty16, ben_amt16)
) bene(typ, amt)
WHERE bene.typ IS NOT NULL AND bene.amt IS NOT null


With this you have a more normalized form that should be easier to join to the other two tables. If this will work, let's start here. We'll then work up to the final form, which will be a dynamic pivot using the labels from the PRUnBenDedCd table.
Go to Top of Page

adalius
Starting Member

11 Posts
Posted - 2014-11-24 : 15:23:53
Ok, A) CROSS APPLY is new to me. That's pretty nifty. I'll have to read up on that.

B) In any row, a given benefit code will only show up once in all 16 of the columns. However, there may be multiple rows for the same employee (typically if payroll was hand adjusted).

So I modified the WHERE clause to:
WHERE bene.typ IS NOT NULL AND bene.amt IS NOT null AND bene.typ <> 0 AND mn.updt_fg <>'Y'

This avoids any duplicates that may come about from payroll (which is fine, our current method excludes those flagged 'Y' as well).

So onto the next step, this wacky dynamic pivot thingy.
Go to Top of Page

gbritton
Master Smack Fu Yak Hacker

2780 Posts
Posted - 2014-11-24 : 15:27:17
I think I've got it (you may need some ifnull function calls):


IF OBJECT_ID(N'tempdb.dbo.#temp', 'U') IS NOT NULL 
	DROP TABLE #temp
SELECT co, local, un, empl, ben.typ, sum(ben.amt) ben_total, cd.descr
INTO #temp
FROM dbo.prunionmn mn
CROSS APPLY (
	VALUES (ben_ty1, ben_amt1),
		   (ben_ty2, ben_amt2),
		   (ben_ty3, ben_amt3),
		   (ben_ty4, ben_amt4),
		   (ben_ty5, ben_amt5),
		   (ben_ty6, ben_amt6),
		   (ben_ty7, ben_amt7),
		   (ben_ty8, ben_amt8),
		   (ben_ty9, ben_amt9),
		   (ben_ty10, ben_amt10),
		   (ben_ty11, ben_amt11),
		   (ben_ty12, ben_amt12),
		   (ben_ty13, ben_amt13),
		   (ben_ty14, ben_amt14),
		   (ben_ty15, ben_amt15),
		   (ben_ty16, ben_amt16)
) ben(typ, amt)
JOIN dbo.[PRUnBenDedCd] cd
  ON ben.typ = cd.bended_cd
WHERE ben.typ IN (
	SELECT [bended_cd] 
	FROM dbo.[PRUnBenDedCd]
	)
GROUP BY co, local, un, empl, ben.typ, cd.descr

ORDER BY co, local, un, empl, ben.typ

DECLARE @collist nvarchar(max) = STUFF(
(
SELECT N','+QUOTENAME(descr)
FROM (select distinct typ, descr from #temp) _
ORDER BY typ
FOR XML path('')
)
,1,1,'')


DECLARE @sql nvarchar(max) = N'
SELECT co, local, un, empl,' 
+ @collist +
' FROM ('
+	'SELECT co, local, un, empl, ben_total, descr from #temp'
+ ') _' +
' PIVOT (sum(ben_total) FOR descr IN ('
+ @collist +
')) pvt'
PRINT @sql

EXEC sp_executesql @sql
Go to Top of Page

adalius
Starting Member

11 Posts
Posted - 2014-11-24 : 15:36:27
*jaw hits floor*
Absolutely brilliant!
Quick examination of results seems to indicate this is exactly what I'm looking for. Just have to cross examine against the values the existing one spits out to double check but I think you've done it.

You win the internet for the day!
Go to Top of Page

gbritton
Master Smack Fu Yak Hacker

2780 Posts
Posted - 2014-11-24 : 15:42:22
Yay!
Go to Top of Page

adalius
Starting Member

11 Posts
Posted - 2014-11-24 : 15:54:19
Ok, one more question... if I want to replace NULL benefit amounts in the output set with 0, where do I ISNULL or COALESCE to do that? I tried wrapping ben.amt and SUM(ben.amt) in the first SELECT, neither worked, I tried wrapping each ben_amt<x> in the cross apply, that didn't work, I tried wrapping ben_total in the SELECT above the pivot and SUM(ben_total) in the pivot and neither of those seem to work either...
Go to Top of Page

gbritton
Master Smack Fu Yak Hacker

2780 Posts
Posted - 2014-11-24 : 16:01:36
You'll have 0to have two variables for the @collist (I used one). The other one for the ISNULL variables, so (untested)


DECLARE @isnull_collist nvarchar(max) = STUFF(
(
SELECT N','+ISNULL(QUOTENAME(descr),0)
FROM (select distinct typ, descr from #temp) _
ORDER BY typ
FOR XML path('')
)
,1,1,'')


then use the new variable in the first place where @collist appears in the @sql var:


DECLARE @sql nvarchar(max) = N'
SELECT co, local, un, empl,' 
+ @isnull_collist + ...
Go to Top of Page

adalius
Starting Member

11 Posts
Posted - 2014-11-24 : 16:08:31
Tweaked it to:
DECLARE @isnull_collist nvarchar(max) = STUFF(
(
SELECT N',ISNULL('+QUOTENAME(descr)+',0)'
FROM (select distinct typ, descr from #temp) _
ORDER BY typ
FOR XML path('')
)
,1,1,'')


Then it works. Thanks!
Go to Top of Page

gbritton
Master Smack Fu Yak Hacker

2780 Posts
Posted - 2014-11-24 : 16:20:50
super!
Go to Top of Page

adalius
Starting Member

11 Posts
Posted - 2014-11-24 : 16:32:29
Actually, it dropped column names doing it that way. If anybody ever looks back on this thread looking for an answer, it needs to be:
DECLARE @isnull_collist nvarchar(max) = STUFF(
(
SELECT N',ISNULL('+QUOTENAME(descr)+',0) AS ' + QUOTENAME(descr)
FROM (select distinct typ, descr from #temp) _
ORDER BY typ
FOR XML path('')
)
,1,1,'')
Go to Top of Page
   

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources

Articles

Forums

Blogs

Contact Us

About the Site