October 15, 2009 at 12:13 pm
I have a script (posted below) that I need to find a way to make this into an SSIS package to be able to run automatically to a text file output with a pipe delimiter. I have battled this for months with no success. The issue revolves around the @Pivot Query portion but they are required. I'm open to making it a stored proc if I can find out how to output the results to a pipe delimited text file or other suggestions.
Thank you in advance for your help.
SET NOCOUNT ON
DECLARE @PivotOutervarchar(max),
@PivotInnervarchar(max),
@PivotQueryvarchar(max),
@BigQueryvarchar(max);
SELECT@PivotOuter = ISNULL(@PivotOuter, '') + '[' + CONVERT(varchar, CustomFieldID) + '] AS [' + CustomFieldName + '], '
FROMdbo.CustomFieldsDisplayNames
WHERECustomFieldID >= 500
ANDCulture = 'en-US'
ORDER BY CustomFieldID;
SET @PivotOuter ='SELECT ContactID, ' + LEFT(RTRIM(@PivotOuter), LEN(RTRIM(@PivotOuter)) - 1) +
' FROM (SELECT ContactID, CustomFieldID, [Value] FROM dbo.CustomFieldsContacts) AS SourceTable ';
SELECT@PivotInner = ISNULL(@PivotInner, '') + '[' + CONVERT(varchar, CustomFieldID) + '], '
FROMdbo.CustomFieldsDisplayNames
WHERECustomFieldID >= 500
ANDCulture = 'en-US'
ORDER BY CustomFieldID;
SET @PivotInner = 'PIVOT (MAX([Value]) FOR CustomFieldID IN (' + LEFT(RTRIM(@PivotInner), LEN(RTRIM(@PivotInner)) - 1) + ')) AS PivotTable';
SET @PivotQuery = 'SELECT x.* FROM dbo.Contacts c JOIN (' + @PivotOuter + @PivotInner + ') AS x ON c.ContactID = x.ContactID';
SELECT @BigQuery =
'WITH DeviceList
AS
(
SELECTROW_NUMBER() OVER(ORDER BY ContactID) AS RecordNumber, DeviceID, ContactID, DeviceTypeID, DeviceSubType, ColumnName, [Value]
FROM
(SELECT ContactID, DeviceID, DeviceTypeID, d2.[Description] AS DeviceSubType,
NULLIF(RTRIM(convert(varchar(260), PhoneNumber)), '''')AS PhoneNumber,
NULLIF(RTRIM(convert(varchar(260), Extension)), '''')AS Extension,
NULLIF(RTRIM(convert(varchar(260), PIN)), '''')AS PIN,
NULLIF(RTRIM(convert(varchar(260), EmailAddress)), '''') AS EmailAddress,
NULLIF(RTRIM(CONVERT(varchar(260), d3.Service_Name)), '''') AS PagerService
FROM Devices d1
LEFT JOIN DeviceSubType d2
ONd1.DeviceSubType = d2.DeviceSubType
LEFT JOIN PagerService d3
ONd1.PagerServiceID = d3.PagerServiceID) d
UNPIVOT
([Value] FOR ColumnName IN
(PhoneNumber, Extension, PIN, EmailAddress, PagerService)
) AS unpvt
) -- CTE ends here
SELECTBigQuery.*, PivotQuery.*
FROM
(SELECTc.ContactID,
c.LastName AS [Last Name],
c.FirstName AS [First Name],
c.RemoteLoginID AS [User ID],
c.LoginID AS [Login Name],
c.ImportKey AS [External Linking Key],
CASE c.IsReportDestination
WHEN 1 THEN ''True''
ELSE ''False''
END AS [Report Recipient],
g.GroupName AS [Assigned Department],
a.AddressLine1 AS [Address Line1],
a.City,
a.StateProvince AS [State/Province],
a.PostalCode AS [ZIP/PostalCode],
CountryCodes.CountryName AS [Country/Region],
x.HomePhone1 AS [Home Phone Number1],
x.HomePhone2 AS [Home Phone Number2],
x.HomePhone3 AS [Home Phone Number3],
x.Work1 AS [Work Phone Number1],
x.Work2 AS [Work Phone Number2],
x.Work3 AS [Work Phone Number3],
x.Email1,
x.Email2,
x.Email3,
x.mobileemail1 AS [Mobile Email1],
x.mobileemail2 AS [Mobile Email2],
x.mobileemail3 AS [Mobile Email3],
x.Cell1 AS [Cell Phone Number1],
x.Cell2 AS [Cell Phone Number2],
x.Cell3 AS [Cell Phone Number3],
x.AlpPg1 AS [Alpha Pager1 Number],
x.AlpPg2 AS [Alpha Pager2 Number],
x.AlpPg3 AS [Alpha Pager3 Number],
x.AlpPin1,
x.AlpPin2,
x.AlpPin3,
x.APgrSvc1,
x.APgrSvc2,
x.APgrSvc3,
x.NumPg1 AS [Numeric Pager1 Number],
x.NumPg2 AS [Numeric Pager2 Number],
x.NumPg3 AS [Numeric Pager3 Number],
x.NumPin1,
x.NumPin2,
x.NumPin3,
x.NPgrSvc1,
x.NPgrSvc2,
x.NPgrSvc3,
x.Other1 AS [Other Phone Number1],
x.Other2 AS [Other Phone Number2],
x.Other3 AS [Other Phone Number3],
x.Fax1 AS [Fax Number1],
x.Fax2 AS [Fax Number2],
x.Fax3 AS [Fax Number3]
FROMdbo.Contacts c
LEFT JOIN(
-- Create the CrossTab query
SELECTContactID,
HomePhone1 = ISNULL((SELECT Value FROM DeviceList WHERE RecordNumber = 1 AND DeviceTypeID = 1 AND DeviceSubType = ''Home'' AND ContactID = d.ContactID), ''''),
HomePhone2 = ISNULL((SELECT Value FROM DeviceList WHERE RecordNumber = 2 AND DeviceTypeID = 1 AND DeviceSubType = ''Home'' AND ContactID = d.ContactID), ''''),
HomePhone3 = ISNULL((SELECT Value FROM DeviceList WHERE RecordNumber = 3 AND DeviceTypeID = 1 AND DeviceSubType = ''Home'' AND ContactID = d.ContactID), ''''),
Work1 = ISNULL((SELECT Value FROM DeviceList WHERE RecordNumber = 1 AND DeviceTypeID = 1 AND DeviceSubType = ''Work'' AND ContactID = d.ContactID), ''''),
Work2 = ISNULL((SELECT Value FROM DeviceList WHERE RecordNumber = 2 AND DeviceTypeID = 1 AND DeviceSubType = ''Work'' AND ContactID = d.ContactID), ''''),
Work3 = ISNULL((SELECT Value FROM DeviceList WHERE RecordNumber = 3 AND DeviceTypeID = 1 AND DeviceSubType = ''Work'' AND ContactID = d.ContactID), ''''),
Email1 = ISNULL((SELECT Value FROM DeviceList WHERE RecordNumber = 1 AND DeviceTypeID = 2 AND ContactID = d.ContactID), ''''),
Email2 = ISNULL((SELECT Value FROM DeviceList WHERE RecordNumber = 2 AND DeviceTypeID = 2 AND ContactID = d.ContactID), ''''),
Email3 = ISNULL((SELECT Value FROM DeviceList WHERE RecordNumber = 3 AND DeviceTypeID = 2 AND ContactID = d.ContactID), ''''),
MobileEmail1 = ISNULL((SELECT Value FROM DeviceList WHERE RecordNumber = 1 AND DeviceTypeID = 6 AND ContactID = d.ContactID), ''''),
MobileEmail2 = ISNULL((SELECT Value FROM DeviceList WHERE RecordNumber = 2 AND DeviceTypeID = 6 AND ContactID = d.ContactID), ''''),
MobileEmail3 = ISNULL((SELECT Value FROM DeviceList WHERE RecordNumber = 3 AND DeviceTypeID = 6 AND ContactID = d.ContactID), ''''),
NumPg1 = ISNULL((SELECT Value FROM DeviceList WHERE RecordNumber = 1 AND DeviceTypeID = 4 AND ColumnName = ''PhoneNumber'' AND ContactID = d.ContactID), ''''),
NumPin1 = ISNULL((SELECT Value FROM DeviceList WHERE RecordNumber = 1 AND DeviceTypeID = 4 AND ColumnName = ''PIN'' AND ContactID = d.ContactID), ''''),
NPgrSvc1 = ISNULL((SELECT Value FROM DeviceList WHERE RecordNumber = 1 AND DeviceTypeID = 4 AND ColumnName = ''PagerService'' AND ContactID = d.ContactID), ''''),
NumPg2 = ISNULL((SELECT Value FROM DeviceList WHERE RecordNumber = 2 AND DeviceTypeID = 4 AND ColumnName = ''PhoneNumber'' AND ContactID = d.ContactID), ''''),
NumPin2 = ISNULL((SELECT Value FROM DeviceList WHERE RecordNumber = 2 AND DeviceTypeID = 4 AND ColumnName = ''PIN'' AND ContactID = d.ContactID), ''''),
NPgrSvc2 = ISNULL((SELECT Value FROM DeviceList WHERE RecordNumber = 2 AND DeviceTypeID = 4 AND ColumnName = ''PagerService'' AND ContactID = d.ContactID), ''''),
NumPg3 = ISNULL((SELECT Value FROM DeviceList WHERE RecordNumber = 3 AND DeviceTypeID = 4 AND ColumnName = ''PhoneNumber'' AND ContactID = d.ContactID), ''''),
NumPin3 = ISNULL((SELECT Value FROM DeviceList WHERE RecordNumber = 3 AND DeviceTypeID = 4 AND ColumnName = ''PIN'' AND ContactID = d.ContactID), ''''),
NPgrSvc3 = ISNULL((SELECT Value FROM DeviceList WHERE RecordNumber = 3 AND DeviceTypeID = 4 AND ColumnName = ''PagerService'' AND ContactID = d.ContactID), ''''),
AlpPg1 = ISNULL((SELECT Value FROM DeviceList WHERE RecordNumber = 1 AND DeviceTypeID = 5 AND ColumnName = ''PhoneNumber'' AND ContactID = d.ContactID), ''''),
AlpPin1 = ISNULL((SELECT Value FROM DeviceList WHERE RecordNumber = 1 AND DeviceTypeID = 5 AND ColumnName = ''PIN'' AND ContactID = d.ContactID), ''''),
APgrSvc1 = ISNULL((SELECT Value FROM DeviceList WHERE RecordNumber = 1 AND DeviceTypeID = 5 AND ColumnName = ''PagerService'' AND ContactID = d.ContactID), ''''),
AlpPg2 = ISNULL((SELECT Value FROM DeviceList WHERE RecordNumber = 2 AND DeviceTypeID = 5 AND ColumnName = ''PhoneNumber'' AND ContactID = d.ContactID), ''''),
AlpPin2 = ISNULL((SELECT Value FROM DeviceList WHERE RecordNumber = 2 AND DeviceTypeID = 5 AND ColumnName = ''PIN'' AND ContactID = d.ContactID), ''''),
APgrSvc2 = ISNULL((SELECT Value FROM DeviceList WHERE RecordNumber = 2 AND DeviceTypeID = 5 AND ColumnName = ''PagerService'' AND ContactID = d.ContactID), ''''),
AlpPg3 = ISNULL((SELECT Value FROM DeviceList WHERE RecordNumber = 3 AND DeviceTypeID = 5 AND ColumnName = ''PhoneNumber'' AND ContactID = d.ContactID), ''''),
AlpPin3 = ISNULL((SELECT Value FROM DeviceList WHERE RecordNumber = 3 AND DeviceTypeID = 5 AND ColumnName = ''PIN'' AND ContactID = d.ContactID), ''''),
APgrSvc3 = ISNULL((SELECT Value FROM DeviceList WHERE RecordNumber = 3 AND DeviceTypeID = 5 AND ColumnName = ''PagerService'' AND ContactID = d.ContactID), ''''),
Cell1 = ISNULL((SELECT Value FROM DeviceList WHERE RecordNumber = 1 AND DeviceTypeID = 1 AND DeviceSubType = ''Cell'' AND ContactID = d.ContactID), ''''),
Cell2 = ISNULL((SELECT Value FROM DeviceList WHERE RecordNumber = 2 AND DeviceTypeID = 1 AND DeviceSubType = ''Cell'' AND ContactID = d.ContactID), ''''),
Cell3 = ISNULL((SELECT Value FROM DeviceList WHERE RecordNumber = 3 AND DeviceTypeID = 1 AND DeviceSubType = ''Cell'' AND ContactID = d.ContactID), ''''),
Other1 = ISNULL((SELECT Value FROM DeviceList WHERE RecordNumber = 1 AND DeviceTypeID = 1 AND DeviceSubType = ''Other'' AND ContactID = d.ContactID), ''''),
Other2 = ISNULL((SELECT Value FROM DeviceList WHERE RecordNumber = 2 AND DeviceTypeID = 1 AND DeviceSubType = ''Other'' AND ContactID = d.ContactID), ''''),
Other3 = ISNULL((SELECT Value FROM DeviceList WHERE RecordNumber = 3 AND DeviceTypeID = 1 AND DeviceSubType = ''Other'' AND ContactID = d.ContactID), ''''),
Fax1 = ISNULL((SELECT Value FROM DeviceList WHERE RecordNumber = 1 AND DeviceTypeID = 1 AND DeviceSubType = ''Fax'' AND ContactID = d.ContactID), ''''),
Fax2 = ISNULL((SELECT Value FROM DeviceList WHERE RecordNumber = 2 AND DeviceTypeID = 1 AND DeviceSubType = ''Fax'' AND ContactID = d.ContactID), ''''),
Fax3 = ISNULL((SELECT Value FROM DeviceList WHERE RecordNumber = 3 AND DeviceTypeID = 1 AND DeviceSubType = ''Fax'' AND ContactID = d.ContactID), '''')
FROMDeviceList d
GROUP BY ContactID) AS x
ON c.ContactID = x.ContactID
LEFT JOIN dbo.Addresses AS a ON c.ContactID = a.ContactID
LEFT JOIN dbo.CountryCodes ON a.CountryCode = CountryCodes.CountryCode
LEFT JOIN dbo.Groups g ON c.DepartmentID = g.GroupID) AS BigQuery
LEFT JOIN ( ' + @PivotQuery + ') AS PivotQuery ON BigQuery.ContactID = PivotQuery.ContactID
ORDER BY BigQuery.[Last Name], BigQuery.[First Name]'
EXEC (@BigQuery)
Viewing post 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply