Skip to main content
Database Administrators

Return to Question

SQL tag cleanup: Improved formatting and language; edited tags, title
Source Link
Paul White ♦
Paul White ♦
  • 90.3k
  • 30
  • 424
  • 663

SQL Pivot column query

I'm trying to figure out how to create a From and To column based on ID.LocationID Columncolumn. I'm

I'm hoping with a suggestion this can be done (iI can figure it out) without having to create sample tables and data as that would take me all day. (fairly complicated query) I'll paste basic structure to give you an idea of what i'm doing...:

Basically i'mI'm selecting from huge table (in ctea CTE) then selecting from this and joining on to other tables below. Sample output:

As you can see i, I have 4 rows and 2 sets. Where the CREATEDDATETIMECREATEDDATETIME is the same I want to combine the rows and add a 'Previous Location' column based on ascending status receipt order. (Status receipt identifies previous row) 

How can this be done? Thanks for your help. Apologies for formatting I don't know how to format dataThis will end up in this..SSRS.

SQL Pivot column

I'm trying to figure out how to create a From and To column based on ID.LocationID Column. I'm hoping with a suggestion this can be done (i can figure it out) without having to create sample tables and data as that would take me all day. (fairly complicated query) I'll paste basic structure to give you an idea of what i'm doing...

Basically i'm selecting from huge table (in cte) then selecting from this and joining on to other tables below. Sample output

As you can see i have 4 rows and 2 sets. Where the CREATEDDATETIME is the same I want to combine the rows and add a 'Previous Location' column based on ascending status receipt order. (Status receipt identifies previous row) How can this be done? Thanks for your help. Apologies for formatting I don't know how to format data in this...

Pivot column query

I'm trying to figure out how to create a From and To column based on ID.LocationID column.

I'm hoping with a suggestion this can be done (I can figure it out) without having to create sample tables and data as that would take me all day. (fairly complicated query) I'll paste basic structure to give you an idea of what i'm doing:

Basically I'm selecting from huge table (in a CTE) then selecting from this and joining on to other tables below. Sample output:

As you can see, I have 4 rows and 2 sets. Where the CREATEDDATETIME is the same I want to combine the rows and add a 'Previous Location' column based on ascending status receipt order. (Status receipt identifies previous row) 

How can this be done? This will end up in SSRS.

edited tags
Link
McNets
McNets
  • 23.9k
  • 11
  • 50
  • 89
Source Link
jhowe
jhowe
  • 281
  • 1
  • 6
  • 19

SQL Pivot column

I'm trying to figure out how to create a From and To column based on ID.LocationID Column. I'm hoping with a suggestion this can be done (i can figure it out) without having to create sample tables and data as that would take me all day. (fairly complicated query) I'll paste basic structure to give you an idea of what i'm doing...

;WITH    ITR
          AS ( SELECT   CREATEDDATETIME ,
                        CREATEDBY ,
                        INVENTID ,
                        ITO.REFERENCECATEGORY ,
                        IT.STATUSRECEIPT
               FROM     dbo.TRANS IT
                        INNER JOIN TRANSORIGIN ITO ON IT.TRANSORIGIN = ITO.RECID
                                                            AND ITO.REFCATEGORY IN (
                                                            2, 6 )
               WHERE    CREATEDDATETIME > DATEADD(MONTH, -1, GETDATE())
             )
    SELECT  ITR.CREATEDDATETIME ,
            ITR.CREATEDBY AS [User] ,
            YPT.PALLETNUMBER AS PalletNumber ,
            ID.LOCATIONID AS [Current Location] ,
            ID.PALLETID AS Pallet ,
            WOT.DEPOTNAME AS Depot ,
            ITR.REFERENCECATEGORY ,
            ITR.STATUSRECEIPT
           
    FROM    dbo.PALLETTABLE AS YPT
            INNER JOIN dbo.PALLET AS WP ON YPT.PALLETID = WP.PALLETID
            LEFT OUTER JOIN dbo.DIM AS ID ON WP.PALLETID = ID.PALLETID
                                                   AND ID.BATCHID <> ''
            LEFT OUTER JOIN dbo.ORDER AS WOT ON ID.DIMID = WOT.DIMID
            LEFT OUTER JOIN ITR ON ID.DIMID = ITR.DIMID
            ORDER BY WP.PALLETNUMBER

Basically i'm selecting from huge table (in cte) then selecting from this and joining on to other tables below. Sample output

CREATEDDATETIME        User PalletNo    Current Location    Pallet  Depot   REFERENCECATEGORY   STATUSRECEIPT
2014-10-10 08:50:10.000 A  C2002    CRK2    1285952 NULL    6   0
2014-10-10 08:50:10.000 A  C2002    300FR   1285952 NULL    6   1
2014-10-10 09:07:16.000 B  C2002    300FR   1285952 NULL    6   0
2014-10-10 09:07:16.000 B  C2002    23IS1   1285952 NULL    6   1

As you can see i have 4 rows and 2 sets. Where the CREATEDDATETIME is the same I want to combine the rows and add a 'Previous Location' column based on ascending status receipt order. (Status receipt identifies previous row) How can this be done? Thanks for your help. Apologies for formatting I don't know how to format data in this...