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
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy