CODEWITHSUNDEEP
sqlsql-serversql-server-2008sql-server-2005
AB Vyas
AB Vyas

Reputation: 2389

Select Single column value in multiple column?

I have some data in my table as follows:

 ID     Name
  2    219SUN_BV_Secure_Gateway.pdf
  3    197FDD_BV_Secure_Gateway.pdf
  5    225RQB_BV_Secure_Gateway.pdf
  6    A_00025_Q1_2012.pdf         
  7    A_00025_Q2_2012.pdf         
  8    A_00025_Q3_2011.pdf         
  9    C_00025_Q3_2011_PostLLC.pdf
 10    B_00025_Q3_2011.pdf

I want to fetch the data as per the following requirement:

I used this query:

SELECT 
    CASE 
        WHEN DocumentFile LIKE 'A%' THEN DocumentFile 
    END as DocFile_A,
    CASE
        WHEN DocumentFile LIKE 'B%' THEN DocumentFile 
    END as DocFile_B,
    CASE 
        WHEN DocumentFile LIKE 'C%' THEN DocumentFile 
    END as DocFile_C 
FROM 
    RFP_DocumentVault

This returns me the following results:

DocFile_A              DocFile_B          DocFile_C
 NULL          NULL             NULL
 NULL          NULL             NULL
 NULL          NULL             NULL
 A_00025_Q1_2012.pdf   NULL             NULL
 A_00025_Q2_2012.pdf   NULL             NULL
 A_00025_Q3_2011.pdf   NULL             NULL
 NULL          NULL           C_00025_Q3_2011_Post Partners II, LLC.pdf
 NULL          B_00025_Q3_2011.pdf        NULL

But I want results as follows:

 DocFile_A            DocFile_B                 DocFile_C
 A_00025_Q1_2012.pdf  B_00025_Q3_2011.pdf     C_00025_Q3_2011_Post Partners II, LLC.pdf
 A_00025_Q2_2012.pdf  NULL                      NULL
 A_00025_Q3_2011.pdf  NULL                      NULL

Any idea how I can do this?

Upvotes: 1

Views: 178

Answers (2)

GolezTrol
GolezTrol

Reputation: 116100

Weird requirements. It seems to me a display issue and not something that a query should solve, but ok. I don't have SQL Server here, but try something like this:

select
  DocFile_A, DocFile_B, DocFile_C
from
    (select
      row_number() over (order by DocumentFile) as RowNum_A,
      DocumentFile as DocFile_A
    from
      RFP_DocumentVault
    where
      DocumentFile like 'A%') A
    full outer join
    (select
      row_number() over (order by DocumentFile) as RowNum_B,
      DocumentFile as DocFile_B
    from
      RFP_DocumentVault
    where
      DocumentFile like 'B%') B on RowNum_B = RowNum_A
    full outer join
    (select
      row_number() over (order by DocumentFile) as RowNum_C,
      DocumentFile as DocFile_C
    from
      RFP_DocumentVault
    where
      DocumentFile like 'C%') C on RowNum_C = RowNum_A or RowNum_C = RowNum_B
order by
  RowNum_A, RowNum_B, RowNum_C

Upvotes: 3

Andriy M
Andriy M

Reputation: 77657

Agree with @GolezTrol, this is something that should perhaps be solved at the presentation level. But if you are absolutely sure you need to do that in SQL, here's an alternative to their solution:

WITH ranked AS (
  SELECT
    DocumentFile,
    grp = 'DocFile_' + LEFT(DocumentFile, 1),
    rnk = ROW_NUMBER() OVER (
      PARTITION BY LEFT(DocumentFile, 1)
      ORDER BY DocumentFile
    )
  FROM RFP_DocumentVault
)
SELECT *
FROM ranked
PIVOT (
  MAX(DocumentFile) FOR grp IN (DocFile_A, DocFile_B, DocFile_C)
) p
;

There's a live demo at SQL Fiddle too.

Upvotes: 6

Related Questions