Solved

CrossTab View?

Posted on 2016-08-16
3
33 Views
Last Modified: 2016-08-19
In my SQL DB I have a table 'tblInstallPay_TaxAuthorities'.  The pertinent fields in the table are 'InstallPayHdrID' (Int) and 'Parcel' nvarchar(30).

A given InstallPayHDrID may appear multiple times.  For a given InstallPayHdrID a Parcel may appear mulltiple times.

'ID'     'Parcel'
123    Parcel1
123   Parcel2
123   Parcel1
123   Parcel3


I have reporting requirement to list all of the Parcels associated with an InstallPayHdrID on one line.

So if InstallPayHdrID 123 has 3 parcels associated it would show as
123     Parcel1 Parcel2  Parcel3

My initial plan was to write a routine reading a view that groups the InstallPayHDrID and Parcels.

For each ID I would lop thru building a sting of the associated parcels.

I'm wondering if there is a way to create a view that will do this for me.  Each returned line in the view would have two fields.
1 The InstallPayHDrID
2. A string of all the parcels associated with that InstallPayHdrID, with each Parcel only being listed once.

Can it be done?  If not I'll just go back to my looping routine.
0
Comment
Question by:mlcktmguy
3 Comments
 
LVL 65

Accepted Solution

by:
Jim Horn earned 250 total points
Comment Utility
To pull this off you can mimic the T-SQL code in the article T-SQL:  Normalized data to a single comma delineated string and back, top section, replacing all the table and column names to match your situation, and replacing the comma with a single space.
0
 
LVL 69

Assisted Solution

by:ScottPletcher
ScottPletcher earned 250 total points
Comment Utility
I strongly suggest using an inline-table-valued function for best performance and ease of use, which you use a CROSS APPLY to invoke when needed  For example:

SET ANSI_NULLS ON;
SET QUOTED_IDENTIFIER ON;
GO
CREATE FUNCTION dbo.Get_InstallPay_Parcel_List
(
    @ID int
)
RETURNS TABLE
AS
RETURN (
    SELECT '' + Parcel AS Parcel_List
    FROM (
        SELECT DISTINCT Parcel
        FROM dbo.tblInstallPay_TaxAuthorities
        WHERE ID = @ID
    ) AS derived
    ORDER BY Parcel
    FOR XML PATH('')
);
GO

--then to use the function:
SELECT mt.*, gipl.Parcel_List
FROM dbo.main_table mt
CROSS APPLY dbo.Get_InstallPay_Parcel_List (mt.ID) gipl
GO
0
 
LVL 1

Author Closing Comment

by:mlcktmguy
Comment Utility
Thank you
0

Featured Post

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
This article describes how to use the timestamp of existing data in a database to allow Tableau to calculate the prior work day instead of relying on case statements or if statements to calculate the days of the week.
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

743 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now