Split an sql column into two based on another columns values

Question

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 :)

It is not possible because your schema have not condition to link Image_URL with Title_Name in single row — Slava Rozhnev
– Slava Rozhnev, Commented Dec 7, 2021 at 9:59
I am not sure I fully understand what you mean. You are saying that to split these two columns into four is not possible? — BoogaBooga
– BoogaBooga, Commented Dec 7, 2021 at 10:03
How do you determine that Image_URL from first table, third row goes to the result table, first row in result and not second? — Salman Arshad
– Salman Arshad, Commented Dec 7, 2021 at 10:05
Hi BoogaBooga, does it matter which title name is in the same row with which Imgage_URL? or can they be matched randomly? — Sebastian ten Berge
– Sebastian ten Berge, Commented Dec 7, 2021 at 10:06
What should be in case when count of Image_URL not equal to count of Title_Name? — Slava Rozhnev
– Slava Rozhnev, Commented Dec 7, 2021 at 10:10

Slava Rozhnev · Accepted Answer · 2021-12-07 10:30:49Z

2

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

edited Dec 7, 2021 at 10:30

answered Dec 7, 2021 at 10:22

Slava Rozhnev

10.2k6 gold badges27 silver badges43 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

BoogaBooga Over a year ago

Thank you Slava yes this did the trick! Could you maybe help me further by explaining your thought process a little? If I understand it correctly you essentially built two queries and then combined them together?

Slava Rozhnev Over a year ago

Yes, you right. 2 queries generates 2 data sets and join combine their. Look one more example with FULL OUTER JOIN

Stu · Accepted Answer · 2021-12-07 10:37:43Z

2

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.

edited Dec 7, 2021 at 10:37

answered Dec 7, 2021 at 10:30

Stu

32.7k6 gold badges17 silver badges34 bronze badges

1 Comment

BoogaBooga Over a year ago

Hi Stu thanks for your answer Im working on implementing it :)

Collectives™ on Stack Overflow

Split an sql column into two based on another columns values

2 Answers 2

2 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related