February 10, 2012 at 6:52 am
I am running a select statement to get the field name and the associated fieldID and want to create a dynamic select statement to execute. These are the requirements:
Declare @Fields as varchar(4000)
set @Fields = 'Audit Number,Audit Year,Client Name,status'
Select '[' + Ltrim(Rtrim(str(prop_id))) + ']', '[' + prop_name + ']'
from propdef
Where Exists (select Item from PRGX_AS_UTILITY.dbo.DelimitedSplit8k(@Fields, ',')
Where Item = prop_name )
This returns:
prop_id(No column name)
[15][Client Name]
[16][Audit Year]
[17][Audit Number]
[133] [status]
I want to create a dynamic tsql string like this so that I can execute the string. state (133) is the only one that will need to be in a Case statement :
Select tocid as [Claim ID]
, [15] AS [Client Name]
,[16] AS [Audit Year]
,[17] AS [Audit Number]
,CASEWHEN [133] IN ('AP','XPV','XPA','XRC','XRJ') THEN 'Potential'
WHEN [133] IN ('CDL', 'CRS', 'CPA', 'CRJ', 'CRV', 'CHL') THEN 'Pending Client'
WHEN [133] IN ('VDL', 'VPR', 'VPA', 'VDP', 'VRJ') THEN 'Pending Vendor'
WHEN [133] IN ('PRV', 'PRP', 'PHL', 'PSB', 'PPO') THEN 'Posting'
WHEN [133] IN ('IRD', 'INV', 'IDP', 'INP') THEN 'Invoicing'
WHEN [133] IN ('LDB', 'LDS', 'LBC', 'LBV') THEN 'Vendor Collection'
WHEN [133] IN ('OBL', 'OPH', 'ORJ') THEN 'Other'
END AS [Stage]
From PropVal
February 10, 2012 at 7:13 am
Something like this should do: -
DECLARE @Fields AS VARCHAR(4000)
SET @Fields = 'Audit Number,Audit Year,Client Name,status'
DECLARE @SQLstmt NVARCHAR(MAX)
--Do the dynamic bit
SELECT @SQLstmt = COALESCE(@SQLstmt, '') + ',' + CHAR(13) + CHAR(10) + QUOTENAME(Ltrim(Rtrim(str(prop_id)))) +
' AS ' + QUOTENAME(prop_name)
FROM propdef
WHERE EXISTS (
SELECT Item
FROM PRGX_AS_UTILITY.dbo.DelimitedSplit8k(@Fields, ',')
WHERE Item = prop_name
)
AND Ltrim(Rtrim(str(prop_id))) <> '133'
--Do the less dynamic bit
SELECT @SQLstmt = STUFF(@SQLstmt, 1, 3, '') + ',' + CHAR(13) + CHAR(10) +
'CASE WHEN ' + QUOTENAME(Ltrim(Rtrim(str(prop_id)))) + ' IN (''AP'',''XPV'',''XPA'',''XRC'',''XRJ'') THEN ''Potential''' + CHAR(13) + CHAR(10) +
'WHEN ' + QUOTENAME(Ltrim(Rtrim(str(prop_id)))) + ' IN (''CDL'',''CRS'',''CPA'',''CRJ'',''CRV'', ''CHL'') THEN ''Pending Client''' + CHAR(13) + CHAR(10) +
'WHEN ' + QUOTENAME(Ltrim(Rtrim(str(prop_id)))) + ' IN (''VDL'',''VPR'',''VPA'',''VDP'',''VRJ'') THEN ''Pending Vendor''' + CHAR(13) + CHAR(10) +
'WHEN ' + QUOTENAME(Ltrim(Rtrim(str(prop_id)))) + ' IN (''PRV'',''PRP'',''PHL'',''PSB'',''PPO'') THEN ''Posting''' + CHAR(13) + CHAR(10) +
'WHEN ' + QUOTENAME(Ltrim(Rtrim(str(prop_id)))) + ' IN (''IRD'',''INV'',''IDP'',''INP'') THEN ''Invoicing''' + CHAR(13) + CHAR(10) +
'WHEN ' + QUOTENAME(Ltrim(Rtrim(str(prop_id)))) + ' IN (''LDB'',''LDS'',''LBC'',''LBV'') THEN ''Vendor Collection''' + CHAR(13) + CHAR(10) +
'WHEN ' + QUOTENAME(Ltrim(Rtrim(str(prop_id)))) + ' IN (''OBL'',''OPH'',''ORJ'') THEN ''Other''' + CHAR(13) + CHAR(10) +
'END AS [Stage]'
FROM propdef
WHERE EXISTS (
SELECT Item
FROM PRGX_AS_UTILITY.dbo.DelimitedSplit8k(@Fields, ',')
WHERE Item = prop_name
)
AND Ltrim(Rtrim(str(prop_id))) = '133'
--Do the static bit
SET @SQLstmt = 'SELECT tocid AS [Claim ID],' + CHAR(13) + CHAR(10) + @SQLstmt + CHAR(13) + CHAR(10) +
'FROM PropVal'
EXECUTE sp_executesql @SQLstmt
February 10, 2012 at 8:43 am
Thanks!
so, I edited my query ...and this is my full query. see my note All the way at the bottom.
DECLARE @Fields AS VARCHAR(4000)
Declare @ClientName AS VARCHAR(100)
Declare @AuditYear AS VARCHAR(50)
SET @Fields = 'Audit Number,Audit Year,Client Name,status'
SET @ClientName = '''AAFES'''
SET @AuditYear = '''2010'''
DECLARE @SQLstmt NVARCHAR(MAX)
--Do the dynamic bit
SELECT @SQLstmt = COALESCE(@SQLstmt, '') + ',' + CHAR(13) + CHAR(10) + QUOTENAME(Ltrim(Rtrim(str(prop_id)))) +
' AS ' + QUOTENAME(prop_name)
FROM propdef
WHERE EXISTS (
SELECT Item
FROM PRGX_AS_UTILITY.dbo.DelimitedSplit8k(@Fields, ',')
WHERE Item = prop_name
)
AND Ltrim(Rtrim(str(prop_id))) <> '133'
SELECT @SQLstmt = STUFF(@SQLstmt, 1, 3, '') + ',' + CHAR(13) + CHAR(10) +
'CASE WHEN ' + QUOTENAME(Ltrim(Rtrim(str(prop_id)))) + ' IN (''AP'',''XPV'',''XPA'',''XRC'',''XRJ'') THEN ''Potential''' + CHAR(13) + CHAR(10) +
'WHEN ' + QUOTENAME(Ltrim(Rtrim(str(prop_id)))) + ' IN (''CDL'',''CRS'',''CPA'',''CRJ'',''CRV'', ''CHL'') THEN ''Pending Client''' + CHAR(13) + CHAR(10) +
'WHEN ' + QUOTENAME(Ltrim(Rtrim(str(prop_id)))) + ' IN (''VDL'',''VPR'',''VPA'',''VDP'',''VRJ'') THEN ''Pending Vendor''' + CHAR(13) + CHAR(10) +
'WHEN ' + QUOTENAME(Ltrim(Rtrim(str(prop_id)))) + ' IN (''PRV'',''PRP'',''PHL'',''PSB'',''PPO'') THEN ''Posting''' + CHAR(13) + CHAR(10) +
'WHEN ' + QUOTENAME(Ltrim(Rtrim(str(prop_id)))) + ' IN (''IRD'',''INV'',''IDP'',''INP'') THEN ''Invoicing''' + CHAR(13) + CHAR(10) +
'WHEN ' + QUOTENAME(Ltrim(Rtrim(str(prop_id)))) + ' IN (''LDB'',''LDS'',''LBC'',''LBV'') THEN ''Vendor Collection''' + CHAR(13) + CHAR(10) +
'WHEN ' + QUOTENAME(Ltrim(Rtrim(str(prop_id)))) + ' IN (''OBL'',''OPH'',''ORJ'') THEN ''Other''' + CHAR(13) + CHAR(10) +
'END AS [Stage]'
FROM propdef
WHERE EXISTS (
SELECT Item
FROM PRGX_AS_UTILITY.dbo.DelimitedSplit8k(@Fields, ',')
WHERE Item = prop_name
)
AND Ltrim(Rtrim(str(prop_id))) = '133'
--Do the static bit
SET @SQLstmt = 'SELECT tocid AS [Claim ID],' + CHAR(13) + CHAR(10) + @SQLstmt + CHAR(13) + CHAR(10) +
'FROM '
Set @SQLstmt = @SQLstmt + '(
SELECT tz.tocid,tz.prop_id,tz.str_val
FROM(
SELECT val.tocid,val.prop_id ,def.prop_name,
CASEWHEN val.prop_id IN (112)THEN CAST(CONVERT(VARCHAR,val.date_val,101) AS VARCHAR(50))
WHEN val.prop_id IN (72,132) THEN CAST(val.num_val AS VARCHAR(50))
ELSE val.str_val
END AS str_val
FROM PROPVAL VAL1
JOIN PROPVAL VAL2 ON VAL1.tocid = VAL2.tocid
JOIN propval val3 ON VAL2.tocid = val3.tocid
join propval val ON VAL1.tocid = val.tocid
join propdef def on val.prop_id = def.prop_id
WHERE val2.prop_id = 15 AND VAL2.str_val = ' + @ClientName +
' AND val1.prop_id = 17
AND val3.prop_id = 16 AND val3.str_val = ' + @AuditYear + '
) as tz
) AS SourceTable
PIVOT
(
MAX(str_val)
FOR prop_id IN ([68],[17],[16],[15],[72],[112],[30],[133])
) AS PivotTable'
* Note: how do I dynamically enter in the bolded section ("[68],[17],[16],[15],[72],[112],[30],[133]")?
In order to get the series of numbers which is the field id, i run the query below
Declare @Fields as varchar(4000)
set @Fields = 'Audit Number,Audit Year,Client Name,status'
Select Ltrim(Rtrim(str(prop_id))) as prop_id, prop_name from propdef
Where Exists (select Item from PRGX_AS_UTILITY.dbo.DelimitedSplit8k(@Fields, ',')
Where Item = prop_name )
and it returns :
prop_idprop_name
15Client Name
16Audit Year
17Audit Number
133Status
February 10, 2012 at 9:05 am
DECLARE @Fields AS VARCHAR(4000)
DECLARE @ClientName AS VARCHAR(100)
DECLARE @AuditYear AS VARCHAR(50)
SET @Fields = 'Audit Number,Audit Year,Client Name,status'
SET @ClientName = '''AAFES'''
SET @AuditYear = '''2010'''
DECLARE @SQLstmt NVARCHAR(MAX)
--Do the dynamic bit
SELECT @SQLstmt = COALESCE(@SQLstmt, '') + ',' + CHAR(13) + CHAR(10) + QUOTENAME(Ltrim(Rtrim(str(prop_id)))) +
' AS ' + QUOTENAME(prop_name)
FROM propdef
WHERE EXISTS (
SELECT Item
FROM PRGX_AS_UTILITY.dbo.DelimitedSplit8k(@Fields, ',')
WHERE Item = prop_name
)
AND Ltrim(Rtrim(str(prop_id))) <> '133'
SELECT @SQLstmt = STUFF(@SQLstmt, 1, 3, '') + ',' + CHAR(13) + CHAR(10) +
'CASE WHEN ' + QUOTENAME(Ltrim(Rtrim(str(prop_id)))) + ' IN (''AP'',''XPV'',''XPA'',''XRC'',''XRJ'') THEN ''Potential''' + CHAR(13) + CHAR(10) +
'WHEN ' + QUOTENAME(Ltrim(Rtrim(str(prop_id)))) + ' IN (''CDL'',''CRS'',''CPA'',''CRJ'',''CRV'', ''CHL'') THEN ''Pending Client''' + CHAR(13) + CHAR(10) +
'WHEN ' + QUOTENAME(Ltrim(Rtrim(str(prop_id)))) + ' IN (''VDL'',''VPR'',''VPA'',''VDP'',''VRJ'') THEN ''Pending Vendor''' + CHAR(13) + CHAR(10) +
'WHEN ' + QUOTENAME(Ltrim(Rtrim(str(prop_id)))) + ' IN (''PRV'',''PRP'',''PHL'',''PSB'',''PPO'') THEN ''Posting''' + CHAR(13) + CHAR(10) +
'WHEN ' + QUOTENAME(Ltrim(Rtrim(str(prop_id)))) + ' IN (''IRD'',''INV'',''IDP'',''INP'') THEN ''Invoicing''' + CHAR(13) + CHAR(10) +
'WHEN ' + QUOTENAME(Ltrim(Rtrim(str(prop_id)))) + ' IN (''LDB'',''LDS'',''LBC'',''LBV'') THEN ''Vendor Collection''' + CHAR(13) + CHAR(10) +
'WHEN ' + QUOTENAME(Ltrim(Rtrim(str(prop_id)))) + ' IN (''OBL'',''OPH'',''ORJ'') THEN ''Other''' + CHAR(13) + CHAR(10) +
'END AS [Stage]'
FROM propdef
WHERE EXISTS (
SELECT Item
FROM PRGX_AS_UTILITY.dbo.DelimitedSplit8k(@Fields, ',')
WHERE Item = prop_name
)
AND Ltrim(Rtrim(str(prop_id))) = '133'
--Do the static bit
SET @SQLstmt = 'SELECT tocid AS [Claim ID],' + CHAR(13) + CHAR(10) + @SQLstmt + CHAR(13) + CHAR(10) +
'FROM '
SET @SQLstmt = @SQLstmt + '(' + CHAR(13) + CHAR(10) +
'SELECT tz.tocid,tz.prop_id,tz.str_val' + CHAR(13) + CHAR(10) +
'FROM (' + CHAR(13) + CHAR(10) +
'SELECT val.tocid,val.prop_id ,def.prop_name,' + CHAR(13) + CHAR(10) +
'CASE WHEN val.prop_id IN (112)THEN CAST(CONVERT(VARCHAR,val.date_val,101) AS VARCHAR(50))' + CHAR(13) + CHAR(10) +
'WHEN val.prop_id IN (72,132) THEN CAST(val.num_val AS VARCHAR(50))' + CHAR(13) + CHAR(10) +
'ELSE val.str_val' + CHAR(13) + CHAR(10) +
'END AS str_val' + CHAR(13) + CHAR(10) +
'FROM PROPVAL VAL1 ' + CHAR(13) + CHAR(10) +
'JOIN PROPVAL VAL2 ON VAL1.tocid = VAL2.tocid' + CHAR(13) + CHAR(10) +
'JOIN propval val3 ON VAL2.tocid = val3.tocid' + CHAR(13) + CHAR(10) +
'join propval val ON VAL1.tocid = val.tocid ' + CHAR(13) + CHAR(10) +
'join propdef def on val.prop_id = def.prop_id' + CHAR(13) + CHAR(10) +
'WHERE val2.prop_id = 15 AND VAL2.str_val = ' + @ClientName + CHAR(13) + CHAR(10) +
'AND val1.prop_id = 17' + CHAR(13) + CHAR(10) +
'AND val3.prop_id = 16 AND val3.str_val = ' + @AuditYear + CHAR(13) + CHAR(10) +
') as tz' + CHAR(13) + CHAR(10)
SET @SQLstmt = @SQLstmt + ') AS SourceTable' + CHAR(13) + CHAR(10) +
'PIVOT' + CHAR(13) + CHAR(10) +
'(' + CHAR(13) + CHAR(10) +
'MAX(str_val)' + CHAR(13) + CHAR(10) +
'FOR prop_id IN (' +
(SELECT STUFF((
SELECT ',' + QUOTENAME(prop_id)
FROM (
SELECT Ltrim(Rtrim(str(prop_id))) AS prop_id, prop_name
FROM propdef
WHERE EXISTS (
SELECT Item
FROM PRGX_AS_UTILITY.dbo.DelimitedSplit8k(@Fields, ',')
WHERE Item = prop_name
)
) a
FOR XML PATH('')
), 1, 1, '')) + ')' + CHAR(13) + CHAR(10) +
') AS PivotTable'
EXECUTE sp_executesql @SQLstmt
February 10, 2012 at 9:25 am
Worked like a charm! thanks!
My next question is. With the table structure below, do you think my approach to make it dynamic works well? I didn't create the db, and think it is not a good structure for a db, but it is what it is and i have to work with it. I am trying to make it easier for anyone to query this data by just entering in the parameters.
This is Propdef table (contains info about the columns in propval table)
prop_idprop_nameprop_typeprop_len
13RegionL13
14Client NumberL7
15Client NameL40
16Audit YearL4
17Audit NumberL11
18Vendor RankI40
19Vendor NumberS30
20Vendor NameS40
21Document TypeL24
22OwnerL40
Prop_types are L(List - which is string), I(integer) , S(string), N(numeric), D(date), H(string)
This is propval table which contains the actual data. The way the data is entered is that each column is a row in the table.
tocidprop_idposstr_valshort_str_valnum_valdate_valbin_val
1106023650PO BOX 374PO BOX 374NULLNULLNULL
1106023660NULLNULLNULLNULLNULL
1106023670NEW YORKNEW YORKNULLNULLNULL
1106023680NYNYNULLNULLNULL
11060236901001210012NULLNULLNULL
1106023700NULLNULL289900.00000NULLNULL
1106023760NULLNULLNULLNULLNULL
1106023790NULLNULLNULLNULLNULL
1106023800NULLNULLNULLNULLNULL
1106023810NULLNULLNULLNULLNULL
February 10, 2012 at 10:01 am
Also, I just turned this into a sproc and I want to allow for multiple AuditYears so how can I change it so that it will accept multiple years? I also want to add another variable so that it filters for AuditNumbers. It will be in the same Location that filters for AuditYear and Client name below..I bolded the section. The only difference is that AuditNumbers is not mandatory, so someone may leave that blank, which should pull ALL AuditNumbers. AuditYear and ClientName is a mandatory variable entry for the query to run.
Here's how I am calling the sproc:
GetClaims 'Audit Number,Audit Year,Client Name,status','''AAFES''', '''2010'''
Create Procedure dbo.GetClaims @Fields As Varchar(4000), @ClientName As Varchar(100), @AuditYear As Varchar(50)
AS
DECLARE @SQLstmt NVARCHAR(MAX)
--Do the dynamic bit
SELECT @SQLstmt = COALESCE(@SQLstmt, '') + ',' + CHAR(13) + CHAR(10) + QUOTENAME(Ltrim(Rtrim(str(prop_id)))) +
' AS ' + QUOTENAME(prop_name)
FROM propdef
WHERE EXISTS (
SELECT Item
FROM PRGX_AS_UTILITY.dbo.DelimitedSplit8k(@Fields, ',')
WHERE Item = prop_name
)
AND Ltrim(Rtrim(str(prop_id))) <> '133'
SELECT @SQLstmt = STUFF(@SQLstmt, 1, 3, '') + ',' + CHAR(13) + CHAR(10) +
'CASE WHEN ' + QUOTENAME(Ltrim(Rtrim(str(prop_id)))) + ' IN (''AP'',''XPV'',''XPA'',''XRC'',''XRJ'') THEN ''Potential''' + CHAR(13) + CHAR(10) +
'WHEN ' + QUOTENAME(Ltrim(Rtrim(str(prop_id)))) + ' IN (''CDL'',''CRS'',''CPA'',''CRJ'',''CRV'', ''CHL'') THEN ''Pending Client''' + CHAR(13) + CHAR(10) +
'WHEN ' + QUOTENAME(Ltrim(Rtrim(str(prop_id)))) + ' IN (''VDL'',''VPR'',''VPA'',''VDP'',''VRJ'') THEN ''Pending Vendor''' + CHAR(13) + CHAR(10) +
'WHEN ' + QUOTENAME(Ltrim(Rtrim(str(prop_id)))) + ' IN (''PRV'',''PRP'',''PHL'',''PSB'',''PPO'') THEN ''Posting''' + CHAR(13) + CHAR(10) +
'WHEN ' + QUOTENAME(Ltrim(Rtrim(str(prop_id)))) + ' IN (''IRD'',''INV'',''IDP'',''INP'') THEN ''Invoicing''' + CHAR(13) + CHAR(10) +
'WHEN ' + QUOTENAME(Ltrim(Rtrim(str(prop_id)))) + ' IN (''LDB'',''LDS'',''LBC'',''LBV'') THEN ''Vendor Collection''' + CHAR(13) + CHAR(10) +
'WHEN ' + QUOTENAME(Ltrim(Rtrim(str(prop_id)))) + ' IN (''OBL'',''OPH'',''ORJ'') THEN ''Other''' + CHAR(13) + CHAR(10) +
'END AS [Stage]'
FROM propdef
WHERE EXISTS (
SELECT Item
FROM PRGX_AS_UTILITY.dbo.DelimitedSplit8k(@Fields, ',')
WHERE Item = prop_name
)
AND Ltrim(Rtrim(str(prop_id))) = '133'
--Do the static bit
SET @SQLstmt = 'SELECT tocid AS [Claim ID],' + CHAR(13) + CHAR(10) + @SQLstmt + CHAR(13) + CHAR(10) +
'FROM '
SET @SQLstmt = @SQLstmt + '(' + CHAR(13) + CHAR(10) +
'SELECT tz.tocid,tz.prop_id,tz.str_val' + CHAR(13) + CHAR(10) +
'FROM (' + CHAR(13) + CHAR(10) +
'SELECT val.tocid,val.prop_id ,def.prop_name,' + CHAR(13) + CHAR(10) +
'CASE WHEN val.prop_id IN (112)THEN CAST(CONVERT(VARCHAR,val.date_val,101) AS VARCHAR(50))' + CHAR(13) + CHAR(10) +
'WHEN val.prop_id IN (72,132) THEN CAST(val.num_val AS VARCHAR(50))' + CHAR(13) + CHAR(10) +
'ELSE val.str_val' + CHAR(13) + CHAR(10) +
'END AS str_val' + CHAR(13) + CHAR(10) +
'FROM PROPVAL VAL1 ' + CHAR(13) + CHAR(10) +
'JOIN PROPVAL VAL2 ON VAL1.tocid = VAL2.tocid' + CHAR(13) + CHAR(10) +
'JOIN propval val3 ON VAL2.tocid = val3.tocid' + CHAR(13) + CHAR(10) +
'join propval val ON VAL1.tocid = val.tocid ' + CHAR(13) + CHAR(10) +
'join propdef def on val.prop_id = def.prop_id' + CHAR(13) + CHAR(10) +
'WHERE val2.prop_id = 15 AND VAL2.str_val = ' + @ClientName + CHAR(13) + CHAR(10) +
'AND val1.prop_id = 17' + CHAR(13) + CHAR(10) +
'AND val3.prop_id = 16 AND val3.str_val = ' + @AuditYear + CHAR(13) + CHAR(10) +
') as tz' + CHAR(13) + CHAR(10)
SET @SQLstmt = @SQLstmt + ') AS SourceTable' + CHAR(13) + CHAR(10) +
'PIVOT' + CHAR(13) + CHAR(10) +
'(' + CHAR(13) + CHAR(10) +
'MAX(str_val)' + CHAR(13) + CHAR(10) +
'FOR prop_id IN (' +
(SELECT STUFF((
SELECT ',' + QUOTENAME(prop_id)
FROM (
SELECT Ltrim(Rtrim(str(prop_id))) AS prop_id, prop_name
FROM propdef
WHERE EXISTS (
SELECT Item
FROM PRGX_AS_UTILITY.dbo.DelimitedSplit8k(@Fields, ',')
WHERE Item = prop_name
)
) a
FOR XML PATH('')
), 1, 1, '')) + ')' + CHAR(13) + CHAR(10) +
') AS PivotTable'
EXECUTE sp_executesql @SQLstmt
February 10, 2012 at 12:19 pm
I want to use this in a query i.e.
Select * from GetClaims 'Audit Number,Audit Year,Claim Number,Claim Key,Client Name,status','''AAFES''','''2010'''
How can I edit my script to do this?
February 13, 2012 at 9:24 am
How can i execute this in a select statement?
I can use this line below to return the records, but I want to be able to use it in any select statment:
GetClaims 'Audit Number,Audit Year,Client Name,status','''AAFES''', '''2010'''
I want to be able to do something like:
Select * from (GetClaims 'Audit Number,Audit Year,Client Name,status','''AAFES''', '''2010''')
Where ...
Order by ...
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply