Results 1 to 17 of 17

Thread: [RESOLVED] Database Rows to Columns in Result

  1. #1

    Thread Starter
    Hyperactive Member
    Join Date
    May 2012
    Posts
    304

    Resolved [RESOLVED] Database Rows to Columns in Result

    Hi there,

    I have the following tables:

    User
    Code:
    id  |  full_name
    
    1  |  Bill Gates
    2  |  Joe Bloggs
    3  |  Travis Barker
    Certificate
    Code:
    id  |  name
    
    1  |  Health & Safety Training
    2  |  MEP
    3  |  18th Edition
    Certificate_Expiry
    Code:
    id  |  user_id  |  certificate_id  |  expiry_date
    
    1  |  1         |  1               |  2021-06-05
    2  |  1         |  3               |  2022-01-12
    3  |  3         |  1               |  2021-07-21
    4  |  2         |  2               |  2022-06-12
    5  |  3         |  3               |  2021-10-12

    I want my result to look like:
    Code:
    full_name       |  Health & Safety Training  |  MEP              |  18th Edition
    
    Bill Gates      |  2021-06-05                |  Null             |  2022-01-12
    Joe Bloggs      |  Null                      |  2022-06-12       |  Null
    Travis Barker   |  2021-07-21                |  Null             |  2021-10-12
    I managed to get the result in SQL Management Studio with the following query:

    Code:
    DECLARE 
                                    @cols AS NVARCHAR(MAX),
                                    @query  AS NVARCHAR(MAX);
                                    select @cols = STUFF((SELECT DISTINCT ',' + QUOTENAME(ce.name)  
    						                                FROM [Company].[dbo].[Certificate_Expiry] cx 
    						                                LEFT JOIN [Company].[dbo].[Certificate] ce 
    							                                ON cx.certificate_id = ce.id
    						                                FOR XML PATH(''), TYPE
    					                                  ).value('.', 'NVARCHAR(MAX)'), 1, 1, '');
                                    set @query 
                                        = N'SELECT full_name, ' + @cols + N' 
                                            FROM 
                                            (
                                                SELECT u.full_name, ce.name, cx.expiry_date 
                                                FROM [Company].[dbo].[Certificate_Expiry] cx 
    			                                LEFT JOIN [Company].[dbo].[Certificate] ce 
    				                                ON cx.certificate_id = ce.id 
    			                                LEFT JOIN [Company].[dbo].[User] u 
    				                                ON cx.user_id = u.id 
                                            ) x
                                            PIVOT 
                                            (
                                                MAX(expiry_date)
                                                FOR name IN (' + @cols + N')
                                            ) p ';
    		                                exec sp_executesql @query;
    But I don't think there is a way of using this query in VB, if someone could clarify that that'd be great.

    So I'm wondering if there is a way of doing it with LINQ or Lamba or if I should change the structure of the database?

    Any help would be appreciated, thanks in advance.

  2. #2
    Super Moderator jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    110,299

    Re: Database Rows to Columns in Result

    Quote Originally Posted by squatman View Post
    I don't think there is a way of using this query in VB, if someone could clarify that that'd be great.
    You'd be wrong. You can execute it like any other query, e.g. using a data reader or a data adapter. You would remove the DECLARE part and add the parameters to the command in VB - lots of information and examples of using parameters with ADO.NET, on this site and elsewhere - but the rest of the SQL will be the same.

    Alternatively, you could do it as two separate queries. The first SELECT statement would be one query, which you could execute by calling ExecuteScalar to get the text. You could then build a String containing the second query in VB and then execute that with ExecuteReader or Fill on a data adapter.

  3. #3

    Thread Starter
    Hyperactive Member
    Join Date
    May 2012
    Posts
    304

    Re: Database Rows to Columns in Result

    jmc, thanks for the response.

    I've used Parameters before e.g.
    vb Code:
    1. cmd.Parameters.Add("@cols", SqlDbType.NVarChar).Value = "value"

    But what would values would I fill them with in this instance, as the variables or parameters are populated within the SQL Query?

    I've tried just declaring them, but it was errored that the values were missing, I've tried breaking the original SQL into two parameters @cols and @query but that didn't work.

    I'd thought about the two queries scenario, but I don't want to take easy way out.

    Thanks.

  4. #4

    Thread Starter
    Hyperactive Member
    Join Date
    May 2012
    Posts
    304

    Re: Database Rows to Columns in Result

    I've just got a bit further setting my parameters direction to Output:

    vb Code:
    1. cmd.Parameters.Add(New SqlParameter("@cols", SqlDbType.NVarChar, 255) With {.Direction = ParameterDirection.Output})
    2.         cmd.Parameters.Add(New SqlParameter("@query", SqlDbType.NVarChar, 255) With {.Direction = ParameterDirection.Output})

    But getting errors with the SQL statement. Hopefully I'm heading in the right direction.

    'Incorrect syntax near 'expiry_date'.'

  5. #5
    Super Moderator jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    110,299

    Re: Database Rows to Columns in Result

    The error message says that there's a syntax error near "expiry_date". You can see what your SQL looks like in that area but, for some reason, you thought it would be better that I couldn't.

  6. #6

    Thread Starter
    Hyperactive Member
    Join Date
    May 2012
    Posts
    304

    Re: Database Rows to Columns in Result

    The SQL ran fine in SQL Management Studio so I didn't think there was anything wrong with the query, I wanted to make sure it wasn't a result of me doing something wrong with the Parameters.

    I've tried prefixing the second instance of 'expiry_date' with 'cx.' but still same error. I've looked at PIVOT examples on Microsoft's website and the only difference I could see was to add 'AS' on my FROM and PIVOT. I'm not really sure what else to try with my seemingly fine query, any further direction would be appreciated.

    vb Code:
    1. Dim sql As String = "set @cols = STUFF((SELECT DISTINCT ',' + QUOTENAME(ce.name)  
    2.                                       FROM [Company].[dbo].[Certificate_Expiry] cx
    3.                                       LEFT JOIN [Company].[dbo].[Certificate] ce
    4.                                        ON cx.certificate_id = ce.id
    5.                                       FOR XML PATH(''), TYPE
    6.                                        ).value('.', 'NVARCHAR(MAX)'), 1, 1, '');
    7.                                 set @query
    8.                                     = N'SELECT full_name, ' + @cols + N'
    9.                                         FROM
    10.                                         (
    11.                                             SELECT u.full_name, ce.name, cx.expiry_date
    12.                                             FROM [Company].[dbo].[Certificate_Expiry] cx
    13.                                            LEFT JOIN [Company].[dbo].[Certificate] ce
    14.                                             ON cx.certificate_id = ce.id
    15.                                            LEFT JOIN [Company].[dbo].[User] u
    16.                                             ON cx.user_id = u.id
    17.                                         ) AS x
    18.                                         PIVOT
    19.                                         (
    20.                                             MAX(cx.expiry_date)
    21.                                             FOR name IN (' + @cols + N')
    22.                                         ) AS p ';
    23.                                   exec sp_executesql @query;"
    24.  
    25.         Dim cmd As New SqlCommand
    26.         cmd.Parameters.Add(New SqlParameter("@cols", SqlDbType.NVarChar, 255) With {.Direction = ParameterDirection.Output})
    27.         cmd.Parameters.Add(New SqlParameter("@query", SqlDbType.NVarChar, 255) With {.Direction = ParameterDirection.Output})
    28.         cmd.CommandText = sql
    29.  
    30.         Dim dt As New DataTable
    31.         dt = fillTest(cmd)
    32.  
    33.         Me.DataGridView1.DataSource = dt

  7. #7
    Super Moderator jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    110,299

    Re: Database Rows to Columns in Result

    I would suggest that you do as you should always do when complex code fails, i.e. take it right back to the most basic form you can and then build it up bit by bit until it fails. You can test parts of that query individually and you can test far simpler pivots to see what works and what doesn't. For the record, I've never used a pivot query in my life, so I'm no expert here. If I had to though, that's what I'd be doing.

  8. #8
    Smooth Moperator techgnome's Avatar
    Join Date
    May 2002
    Posts
    34,532

    Re: Database Rows to Columns in Result

    *koff* Stored Proceedure *koff* *koff* Stored procedure *koff*


    Sorry... yeah, I've done this exact thing before, but never stuffing the query into VB as a string... that's going to be asking for trouble in the furture. Your best bet would be to create it as a stored proc in SQL Server, and then simply execute it. Feed it the parameters it needs, get the results back. Unless there is a reason you can't.


    But in short your string will need to:
    1) Declare all the variables, ie. @cols & @query ... they are not parameters you're passing in, so don't create them as such.
    2) Set the @cols variable
    3) Set the @query variable using @cols
    4) execute the query

    -tg
    * I don't respond to private (PM) requests for help. It's not conducive to the general learning of others.*
    * I also don't respond to friend requests. Save a few bits and don't bother. I'll just end up rejecting anyways.*
    * How to get EFFECTIVE help: The Hitchhiker's Guide to Getting Help at VBF - Removing eels from your hovercraft *
    * How to Use Parameters * Create Disconnected ADO Recordset Clones * Set your VB6 ActiveX Compatibility * Get rid of those pesky VB Line Numbers * I swear I saved my data, where'd it run off to??? *

  9. #9

    Thread Starter
    Hyperactive Member
    Join Date
    May 2012
    Posts
    304

    Re: Database Rows to Columns in Result

    techgnome,

    Thanks for your response, I'm just picking this up again now; I managed to get it working as a stored procedure so thank you. During development for filtering purposes I've realised I need a couple more columns for the current column to allow filtering.

    I will actually need my output table to look like this:

    Code:
    full_name       |  H&S Training (Valid from)  |  H&S Training (Expires)  |  MEP (Valid from)  |  18th Edition (Valid from)    |  MEP (Expires)    |  18th Edition (Expires)
    
    Bill Gates      |  2021-06-05                 |  2022-06-05              |  Null              |  Null                         |  2022-01-12       |  2023-01-12
    Joe Bloggs      |  Null                       |  Null                    |  2022-06-12        |  2023-06-12                   |  Null             |  Null
    Travis Barker   |  2021-07-21                 |  2021-10-12              |  Null              |  Null                         |  2022-07-21       |  2022-10-12
    I'll also need another set of columns e.g. H&S Training (Category) but I'm just trying to get two working for now.

    I know this likely wouldn't give me the columns in the order I've shown above but I'm struggling to get this working:

    Code:
    BEGIN
    SET NOCOUNT ON
    
    DECLARE
    @cols AS NVARCHAR(MAX),
    @cols2 AS NVARCHAR(MAX),
    @cols3 AS NVARCHAR(MAX),
    @query  AS NVARCHAR(MAX);
    
    SET @cols3 = 'ct.[name] + '' (Valid)'' AS ''V'', ct.[name] + '' (Expiry)'' AS ''E''';
    
    SELECT @cols = STUFF((SELECT DISTINCT ',' + QUOTENAME(ct.[name] + ' (Valid)') 
    						FROM [Company].[dbo].[Certificate] c 
    						LEFT JOIN [Company].[dbo].[Certificate_Type] ct 
    							ON c.certificate_type_id = ct.id
    						FOR XML PATH(''), TYPE
    					    ).value('.', 'NVARCHAR(MAX)'), 1, 1, '');
    						
    SELECT @cols2 = STUFF((SELECT DISTINCT ',' + QUOTENAME(ct.[name] + ' (Expiry)') 
    						FROM [Company].[dbo].[Certificate] c 
    						LEFT JOIN [Company].[dbo].[Certificate_Type] ct 
    							ON c.certificate_type_id = ct.id
    						FOR XML PATH(''), TYPE
    					    ).value('.', 'NVARCHAR(MAX)'), 1, 1, '');
    SET @query 
        = N'SELECT full_name, ' + @cols + ', ' + @cols2 + N' 
            FROM 
            (
                SELECT u.full_name, ' + @cols3 + ', c.valid_from_date, c.expiry_date 
                FROM [Company].[dbo].[Certificate] c 
    			LEFT JOIN [Company].[dbo].[Certificate_Type] ct 
    				ON c.certificate_type_id = ct.id 
    			LEFT JOIN [Company].[dbo].[User] u 
    				ON c.user_id = u.id 
            ) x
            PIVOT 
            (
                MAX(valid_from_date)
                FOR name IN (' + @cols + N')
            ) p
            PIVOT 
            (
                MAX(expiry_date)
                FOR name IN (' + @cols2 + N')
            ) p2';
    END
    
    EXEC sp_executesql @query;
    I get the following error:
    Msg 207, Level 16, State 1, Line 14
    Invalid column name 'name'.
    Msg 207, Level 16, State 1, Line 19
    Invalid column name 'name'.
    Even though in the original query there was no problem with the column name "name".

    Any advice or direction with this would be greatly appreciated.

    Thank you.

  10. #10
    Smooth Moperator techgnome's Avatar
    Join Date
    May 2002
    Posts
    34,532

    Re: Database Rows to Columns in Result

    Code:
    SET @cols3 = 'ct.[name] + '' (Valid)'' AS ''V'', ct.[name] + '' (Expiry)'' AS ''E''';
    That's because on this line ct and subsequently .[name] have no context ... in the others, they did because there was a select where ct was aliased, so it had context, allowing .[name] to also have context. But in the above statement, there is no select... on a SET so ct doesn't exist.

    -tg
    * I don't respond to private (PM) requests for help. It's not conducive to the general learning of others.*
    * I also don't respond to friend requests. Save a few bits and don't bother. I'll just end up rejecting anyways.*
    * How to get EFFECTIVE help: The Hitchhiker's Guide to Getting Help at VBF - Removing eels from your hovercraft *
    * How to Use Parameters * Create Disconnected ADO Recordset Clones * Set your VB6 ActiveX Compatibility * Get rid of those pesky VB Line Numbers * I swear I saved my data, where'd it run off to??? *

  11. #11

    Thread Starter
    Hyperactive Member
    Join Date
    May 2012
    Posts
    304

    Re: Database Rows to Columns in Result

    Thanks for the response tg, appreciate it.

    Not sure if I've got the wrong end of the stick, but I updated the SELECTs to the following to include the aliases:

    Code:
    SELECT @cols = STUFF((SELECT DISTINCT ',' + QUOTENAME(ct.[name] + ' (Valid) AS ''V''') 
    						FROM [Company].[dbo].[Certificate] c 
    						LEFT JOIN [Company].[dbo].[Certificate_Type] ct 
    							ON c.certificate_type_id = ct.id
    						FOR XML PATH(''), TYPE
    					    ).value('.', 'NVARCHAR(MAX)'), 1, 1, '');
    						
    SELECT @cols2 = STUFF((SELECT DISTINCT ',' + QUOTENAME(ct.[name] + ' (Expiry) AS ''E''') 
    						FROM [Company].[dbo].[Certificate] c 
    						LEFT JOIN [Company].[dbo].[Certificate_Type] ct 
    							ON c.certificate_type_id = ct.id
    						FOR XML PATH(''), TYPE
    					    ).value('.', 'NVARCHAR(MAX)'), 1, 1, '');
    But I still get the same error.

    Apologies if I'm missing something obvious.

  12. #12

    Thread Starter
    Hyperactive Member
    Join Date
    May 2012
    Posts
    304

    Re: Database Rows to Columns in Result

    Ok I've been playing around and saw the my SELECT @cols where returning the column names with squad brackets around them e.g. [Cert 1 ' (Valid)' AS 'V'] which I thought this may have been causing the issue. I've removed the QUOTENAME to avoid this and also removed the aliases:

    Code:
    SELECT STUFF((SELECT DISTINCT ',' + ct.name + ' (Valid)' 
    						FROM [Company].[dbo].[Certificate] c 
    						LEFT JOIN [Company].[dbo].[Certificate_Type] ct 
    							ON c.certificate_type_id = ct.id
    						FOR XML PATH(''), TYPE
    					    ).value('.', 'NVARCHAR(MAX)'), 1, 1, '');
    My command now looks like this:

    Code:
    BEGIN
    SET NOCOUNT ON
    
    DECLARE
    @cols AS NVARCHAR(MAX),
    @cols2 AS NVARCHAR(MAX),
    @query  AS NVARCHAR(MAX);
    
    
    SELECT @cols = STUFF((SELECT DISTINCT ',' + ct.[name] + ' (Valid)' 
    						FROM [Company].[dbo].[Certificate] c 
    						LEFT JOIN [Company].[dbo].[Certificate_Type] ct 
    							ON c.certificate_type_id = ct.id
    						FOR XML PATH(''), TYPE
    					    ).value('.', 'NVARCHAR(MAX)'), 1, 1, '');
    						
    SELECT @cols2 = STUFF((SELECT DISTINCT ',' + ct.[name] + ' (Expiry)' 
    						FROM [Company].[dbo].[Certificate] c 
    						LEFT JOIN [Company].[dbo].[Certificate_Type] ct 
    							ON c.certificate_type_id = ct.id
    						FOR XML PATH(''), TYPE
    					    ).value('.', 'NVARCHAR(MAX)'), 1, 1, '');
    SET @query 
        = N'SELECT full_name, ' + @cols + ', ' + @cols2 + N' 
            FROM 
            (
                SELECT u.full_name, ct.[name] + '' (Valid)'', ct.[name] + '' (Expiry)'', c.valid_from_date, c.expiry_date 
                FROM [Company].[dbo].[Certificate] c 
    			LEFT JOIN [Company].[dbo].[Certificate_Type] ct 
    				ON c.certificate_type_id = ct.id 
    			LEFT JOIN [Company].[dbo].[User] u 
    				ON c.user_id = u.id 
            ) x
            PIVOT 
            (
                MAX(valid_from_date)
                FOR name IN (' + @cols + N')
            ) p
            PIVOT 
            (
                MAX(expiry_date)
                FOR name IN (' + @cols2 + N')
            ) p2';
    END
    
    EXEC sp_executesql @query;
    But I now receive the following error:

    Msg 102, Level 15, State 1, Line 1
    Incorrect syntax near '1'.
    Msg 102, Level 15, State 1, Line 10
    Incorrect syntax near 'x'.
    Not sure why it's seeing a "1", but the @cols retrieves this
    Cert 1 (Valid),Cert 2 (Valid),Cert 3 (Valid)
    so maybe I do need the QUOTENAME to apply the square brackets?

    Thanks again for any help.

  13. #13

    Thread Starter
    Hyperactive Member
    Join Date
    May 2012
    Posts
    304

    Re: Database Rows to Columns in Result

    Apologies for all the replies, I've been writing the SQL out manually rather then with variables to see exactly what's going on.

    The problem with the "name" is in the PIVOT:

    Code:
            PIVOT 
            (
                MAX(valid_from_date)
                FOR name IN ([Cert 1 (Valid)],[Cert 2 (Valid)],[Cert 3 (Valid)])
            ) p

    I've been trying to concatenate the column name as the rest of the query but it doesn't like the below:

    Code:
            PIVOT 
            (
                MAX(valid_from_date)
                FOR ct.[name] + ' (Valid)' IN ([Cert 1 (Valid)],[Cert 2 (Valid)],[Cert 3 (Valid)])
            ) p
    How do I specify (Valid) and (Expiry) in the FOR of the PIVOT?

    Thank you.

  14. #14

    Thread Starter
    Hyperactive Member
    Join Date
    May 2012
    Posts
    304

    Re: Database Rows to Columns in Result

    Ok I've managed to get the query to execute and now I understand what you was referring to with the ALIAS, with you saying in the SET I was looking in the wrong place.

    Thank you techgnome.

    I've now come to another problem. My final query is:

    Code:
    BEGIN
    SET NOCOUNT ON
    
    DECLARE
    @cols AS NVARCHAR(MAX),
    @cols2 AS NVARCHAR(MAX),
    @query  AS NVARCHAR(MAX);
    
    
    SELECT @cols = STUFF((SELECT DISTINCT ',' + QUOTENAME(ct.[name] + ' (Valid)') 
    						FROM [Company].[dbo].[Certificate] c 
    						LEFT JOIN [Company].[dbo].[Certificate_Type] ct 
    							ON c.certificate_type_id = ct.id
    						FOR XML PATH(''), TYPE
    					    ).value('.', 'NVARCHAR(MAX)'), 1, 1, '');
    						
    SELECT @cols2 = STUFF((SELECT DISTINCT ',' + QUOTENAME(ct.[name] + ' (Expiry)') 
    						FROM [Company].[dbo].[Certificate] c 
    						LEFT JOIN [Company].[dbo].[Certificate_Type] ct 
    							ON c.certificate_type_id = ct.id
    						FOR XML PATH(''), TYPE
    					    ).value('.', 'NVARCHAR(MAX)'), 1, 1, '');
    SET @query 
        = N'SELECT full_name, ' + @cols + ', ' + @cols2 + N' 
            FROM 
            (
                SELECT u.full_name, ct.[name] + '' (Valid)'' AS ''V'', ct.[name] + '' (Expiry)'' AS ''E'', c.valid_from_date, c.expiry_date 
                FROM [Company].[dbo].[Certificate] c 
    			LEFT JOIN [Company].[dbo].[Certificate_Type] ct 
    				ON c.certificate_type_id = ct.id 
    			LEFT JOIN [Company].[dbo].[User] u 
    				ON c.user_id = u.id 
            ) x
            PIVOT 
            (
                MAX(valid_from_date)
                FOR V IN (' + @cols + N')
            ) p
            PIVOT 
            (
                MAX(expiry_date)
                FOR E IN (' + @cols2 + N')
            ) p2';
    END
    
    EXEC sp_executesql @query;
    But my returned table now has a multiple rows for for each person rather than grouping them and a GROUP BY does not work.

    Please see result below.

    Name:  result.png
Views: 122
Size:  11.8 KB

    Thank you.

  15. #15

    Thread Starter
    Hyperactive Member
    Join Date
    May 2012
    Posts
    304

    Re: Database Rows to Columns in Result

    Apologies again, I need to learn a lot more SQL.

    A bit of Googling and creating some more variables with MAX( ) around the columns names I've managed to get it as I want it.

    Final query below, thanks for your time and help:

    Code:
    BEGIN
    SET NOCOUNT ON
    
    DECLARE
    @cols AS NVARCHAR(MAX),
    @cols2 AS NVARCHAR(MAX),
    @test1 AS NVARCHAR(MAX),
    @test2 AS NVARCHAR(MAX),
    @query  AS NVARCHAR(MAX);
    
    
    
    SELECT @cols = STUFF((SELECT DISTINCT ',' + QUOTENAME(ct.[name] + ' (Valid)') 
    						FROM [Company].[dbo].[Certificate] c 
    						LEFT JOIN [Company].[dbo].[Certificate_Type] ct 
    							ON c.certificate_type_id = ct.id
    						FOR XML PATH(''), TYPE
    					    ).value('.', 'NVARCHAR(MAX)'), 1, 1, '');
    						
    SELECT @cols2 = STUFF((SELECT DISTINCT ',' + QUOTENAME(ct.[name] + ' (Expiry)') 
    						FROM [Company].[dbo].[Certificate] c 
    						LEFT JOIN [Company].[dbo].[Certificate_Type] ct 
    							ON c.certificate_type_id = ct.id
    						FOR XML PATH(''), TYPE
    					    ).value('.', 'NVARCHAR(MAX)'), 1, 1, '');
    
    
    SELECT @test1 = STUFF((SELECT DISTINCT ',' + 'MAX(' + QUOTENAME(ct.[name] + ' (Valid)') + ')' 
    						FROM [Company].[dbo].[Certificate] c 
    						LEFT JOIN [Company].[dbo].[Certificate_Type] ct 
    							ON c.certificate_type_id = ct.id
    						FOR XML PATH(''), TYPE
    					    ).value('.', 'NVARCHAR(MAX)'), 1, 1, '');
    						
    SELECT @test2 = STUFF((SELECT DISTINCT ',' + 'MAX(' + QUOTENAME(ct.[name] + ' (Expiry)') + ')' 
    						FROM [Company].[dbo].[Certificate] c 
    						LEFT JOIN [Company].[dbo].[Certificate_Type] ct 
    							ON c.certificate_type_id = ct.id
    						FOR XML PATH(''), TYPE
    					    ).value('.', 'NVARCHAR(MAX)'), 1, 1, '');
    SET @query 
        = N'WITH Pivoted 
    	AS
    	(
    	SELECT full_name, ' + @cols + ', ' + @cols2 + N' 
            FROM 
            (
                SELECT u.full_name, ct.[name] + '' (Valid)'' AS ''V'', ct.[name] + '' (Expiry)'' AS ''E'', c.valid_from_date, c.expiry_date 
                FROM [Company].[dbo].[Certificate] c 
    			LEFT JOIN [Company].[dbo].[Certificate_Type] ct 
    				ON c.certificate_type_id = ct.id 
    			LEFT JOIN [Company].[dbo].[User] u 
    				ON c.user_id = u.id 
            ) x
            PIVOT 
            (
                MAX(valid_from_date)
                FOR V IN (' + @cols + N')
            ) p
            PIVOT 
            (
                MAX(expiry_date)
                FOR E IN (' + @cols2 + N')
            ) p2
    		)
    		SELECT full_name, ' + @test1 + ', ' + @test2 + N'
    		FROM Pivoted 
    		GROUP BY full_name'
    END
    
    EXEC sp_executesql @query;

  16. #16

    Thread Starter
    Hyperactive Member
    Join Date
    May 2012
    Posts
    304

    Re: Database Rows to Columns in Result

    Actually not quite, now my columns don't have a name!! aahhh!!

  17. #17

    Thread Starter
    Hyperactive Member
    Join Date
    May 2012
    Posts
    304

    Re: Database Rows to Columns in Result

    Done. Thank you. Sorry.

    Code:
    SELECT @test1 = STUFF((SELECT DISTINCT ',' + 'MAX(' + QUOTENAME(ct.[name] + ' (Valid)') + ') AS ''' + ct.[name] + ' (Valid)'''  
    						FROM [Company].[dbo].[Certificate] c 
    						LEFT JOIN [Company].[dbo].[Certificate_Type] ct 
    							ON c.certificate_type_id = ct.id
    						FOR XML PATH(''), TYPE
    					    ).value('.', 'NVARCHAR(MAX)'), 1, 1, '');
    						
    SELECT @test2 = STUFF((SELECT DISTINCT ',' + 'MAX(' + QUOTENAME(ct.[name] + ' (Expiry)') + ') AS ''' + ct.[name] + ' (Expiry)'''  
    						FROM [Company].[dbo].[Certificate] c 
    						LEFT JOIN [Company].[dbo].[Certificate_Type] ct 
    							ON c.certificate_type_id = ct.id
    						FOR XML PATH(''), TYPE
    					    ).value('.', 'NVARCHAR(MAX)'), 1, 1, '');

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width