CODEWITHSUNDEEP
sql-servert-sqlpivot
quelquecosa
quelquecosa

Reputation: 910

I need to flatten out SQL Server rows into columns using pivot

I've been trying for a while to use SQL Server pivot but I just don't seem to be getting it right. I've read a bunch of SO answers, but don't understand how pivot works.

I'm writing a stored procedure. I have Table 1 (received as a TVP), and need to make it look like Table 2 (see this image for tables).

Important: the values in Table1.valueTypeID cannot be hard coded into the logic because they can always change. Therefore, the logic must be super dynamic.

Please see the code below. The pivot is at the end of the stored procedure.

            -- Create date: 12/10/2013
            -- Description: select all the contacts associated with received accountPassport
            -- =============================================
            ALTER PROCEDURE [dbo].[selectContactsPropsByAccountPassport] 
                -- Add the parameters for the stored procedure here
                @accountPassport int, 
                @valueTypeFiltersTVP valueTypeFiltersTVP READONLY
            AS
            BEGIN
                -- SET NOCOUNT ON added to prevent extra result sets from
                -- interfering with SELECT statements.
                SET NOCOUNT ON;

                -- Insert statements for procedure here
                DECLARE @accountID int;
                DECLARE @contactsAppAccountPassport int;
                DECLARE @searchResults TABLE
                    (
                        resultContactID int
                    );
                DECLARE @resultContactID int;

                DECLARE @contactsPropsForReturn TABLE
                    (
                        contactID int,
                        valueTypeID int,
                        value varchar(max)
                    );

                create table #contactsPropsForReturnFiltered(contactID int,valueTypeID int, value varchar(max))

                /*
                DECLARE #contactsPropsForReturnFiltered TABLE 
                    (
                        contactID int,
                        valueTypeID int,
                        value varchar(max)
                    );
                */

            --2. get @contactsAppAccountPassport associated with recieved @accountPassport
                -- go into dbo.accounts and get the @accountID associated with this @accountPassport
                SELECT
                    @accountID = ID
                FROM
                    dbo.accounts
                WHERE
                    passport = @accountPassport

                -- go into dbo.accountsProps and get the value (@contactsAppAccountPassport) where valueType=42 and accountID = @accountID
                SELECT
                    @contactsAppAccountPassport = value
                FROM
                    dbo.accountsProps
                WHERE
                    (valueTypeID=42) AND (accountID = @accountID)

            --3. get all the contact ID's from dbo.contacts associated with @contactsAppAccountPassport
                INSERT INTO
                    @searchResults
                SELECT
                    ID
                FROM
                    dbo.contacts
                WHERE
                    contactsAppAccountPassport = @contactsAppAccountPassport

            --4. Get the props of all contact ID's from 3. 

                --start for each loop....our looping object is @resultContactID row. if there are more rows, we keep looping.
                DECLARE searchCursor CURSOR FOR
                    SELECT
                        resultContactID
                    FROM
                        @searchResults

                    OPEN searchCursor

                        FETCH NEXT FROM searchCursor INTO @resultContactID

                        WHILE (@@FETCH_STATUS=0)
                        BEGIN   
                            INSERT INTO
                                @contactsPropsForReturn
                            SELECT
                                contactID,
                                valueTypeID,
                                value
                            FROM
                                dbo.contactsProps
                            WHERE
                                contactID = @resultContactID
                            FETCH NEXT FROM searchCursor INTO @resultContactID
                        END --end of WHILE loop

                    --end of cursor (both CLOSE and DEALLOCATE necessary)
                    CLOSE searchCursor
                    DEALLOCATE searchCursor

                    -- select and return only the props that match with the requested props 
                    -- (we don't want to return all the props, only the ones requested)
                    INSERT INTO
                        #contactsPropsForReturnFiltered
                    SELECT
                        p.contactID,
                        p.valueTypeID,
                        p.value
                    FROM
                        @contactsPropsForReturn as p
                    INNER JOIN
                        @valueTypeFiltersTVP as f
                    ON
                        p.valueTypeID = f.valueTypeID



                    DECLARE @cols AS NVARCHAR(MAX),
                    @query  AS NVARCHAR(MAX);

                    SET @cols = STUFF((SELECT distinct ',' + QUOTENAME(ValueTypeId) 
                        FROM #contactsPropsForReturnFiltered 
                        FOR XML PATH(''), TYPE
                        ).value('.', 'NVARCHAR(MAX)') 
                    ,1,1,'');

            set @query = 'SELECT contactid, ' + @cols + ' from 
                        (
                            select contactid
                                , Value
                               ,ValueTypeId
                            from #contactsPropsForReturnFiltered
                       ) x
                        pivot 
                        (
                             min(Value)
                            for ValueTypeId in (' + @cols + ')
                        ) p ';

            execute(@query);

            END

Upvotes: 2

Views: 1681

Answers (2)

ErikE
ErikE

Reputation: 50241

Why do you need to present the data in this way?

In many cases, clients are better at pivoting than the database engine. For example, SQL Server Reporting Services easily does this with the matrix control. Similarly, if you are coding a web page in, say, Asp.Net, you can run through the recordset quickly to pass your data into a new data representation (meanwhile collecting unique values) and then in a single pass through the new data object spit out the HTML to render the result.

If at all possible, have your client do the pivoting instead of the server.

UPDATE:

If you really want to use table variables in dynamic SQL, you can just fine in SQL Server 2008 and up. Here's an example script:

USE tempdb
GO
CREATE TYPE IDList AS TABLE (
    ID int
);
GO
DECLARE @SQL nvarchar(max);
SET @SQL = 'SELECT * FROM @TransactionIDs WHERE ID >= 4;'
DECLARE @TransactionIDs IDLIst;
INSERT @TransactionIDs VALUES (1), (2), (4), (8), (16);
EXEC sp_executesql @SQL, N'@TransactionIDs IDList READONLY', @TransactionIDs;
GO
DROP TYPE IDList;

Upvotes: 0

sahalMoidu
sahalMoidu

Reputation: 1152

You need to use dynamic pivot in your case. Try the following

create table table1
(
    contactid int,
    ValueTypeId int,
    Value varchar(100)
);

insert into table1 values (56064, 40, 'Issac');
insert into table1 values (56064, 34, '(123)456-7890');
insert into table1 values (56065, 40, 'Lola');
insert into table1 values (56065, 34, '(123)456-7832');
insert into table1 values (56068, 40, 'Mike');
insert into table1 values (56068, 41, 'Gonzalez');
insert into table1 values (56068, 34, '(123)456-7891');


DECLARE @cols AS NVARCHAR(MAX),
    @query  AS NVARCHAR(MAX);

SET @cols = STUFF((SELECT distinct ',' + QUOTENAME(ValueTypeId) 
            FROM table1 
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'');

set @query = 'SELECT contactid, ' + @cols + ' from 
            (
                select contactid
                    , Value
                   ,ValueTypeId
                from table1
           ) x
            pivot 
            (
                 min(Value)
                for ValueTypeId in (' + @cols + ')
            ) p ';


execute(@query);

drop table table1

Upvotes: 2

Related Questions