Split an sql column into two based on another columns values-CodePudding

I have a SQL DB setup like this:

#1 Format

COLUMN 1 (Values)	COLUMN 2 (Identifier)
Title_Name	Collection_Title
Title_Name	Collection_Title
Image_URL	Collection_Image
Image_URL	Collection_Image

I would like to split these 2 columns into four seperate columns like so:

#2 Format

VALUE #1	IDENTIFIER #1	VALUE #2	IDENTIFIER #2
Image_URL	Collection_Image	Title Name	Collection_Title
Image_URL	Collection_Image	Title Name	Collection_Title

So far I have the query:

SELECT  value, identifier
FROM [tableName]
WHERE identifier IN ('Image_URL', 'Title_Name');

and its returning the #1 format, so I have all the information returning from the query. My problem is I would like to have it presented in the #2 Query with each column having a unique name.

How can I expand or improve my query to return the #2 format?

Thank you :)

CodePudding user response：

You can assign a sequential number to each group of values and use a full join. this will give the exact results or null if there are an imbalance of matching rows.

with image as (
    select [value] Value1, identifier Identifier1, Row_Number() over(order by value) rn
    from t
    where [value] = 'Image_URL'
), title as (
    select [value] Value2, identifier Identifier2, Row_Number() over(order by value) rn
    from t
    where [value] = 'Title_Name'
)
select  value1,identifier1, value2, identifier2
from image i 
full join title t on t.rn=i.rn

Demo Fiddle

Note this demo is using Sql Server (no specific RDBMS was indicated) but is compatible with most.

CodePudding user response：

You can use CROSS JOIN and get cartesian :

SELECT * FROM (
  SELECT  value, identifier
  FROM [tableName]
  WHERE identifier = 'Collection_Image'
) Images
CROSS JOIN (
  SELECT  value, identifier
  FROM [tableName]
  WHERE identifier = 'Collection_Title'
) Titles;

Result:

 ============ ================== ============= ================== 
| value      | identifier       | value       | identifier       |
 ============ ================== ============= ================== 
| Image_URL1 | Collection_Image | Title_Name1 | Collection_Title |
 ------------ ------------------ ------------- ------------------ 
| Image_URL2 | Collection_Image | Title_Name1 | Collection_Title |
 ------------ ------------------ ------------- ------------------ 
| Image_URL1 | Collection_Image | Title_Name2 | Collection_Title |
 ------------ ------------------ ------------- ------------------ 
| Image_URL2 | Collection_Image | Title_Name2 | Collection_Title |
 ------------ ------------------ ------------- ------------------

SQL cartesian fiddle

You also can use ROW_NUMBER function as join condition :

SELECT Images.value, Images.identifier, Titles.value, Titles.identifier  
FROM (
  SELECT  value, identifier, row_number() over(order by value) rn
  FROM [tableName]
  WHERE identifier = 'Collection_Image'
) Images
FULL OUTER JOIN (
  SELECT  value, identifier, row_number() over(order by value) rn
  FROM [tableName]
  WHERE identifier = 'Collection_Title'
) Titles ON Images.rn = Titles.rn;

FULL OUTER JOIN