SQLTeam.com | Weblogs | Forums

PIVOT Function for Text

Dear SQL community,

I'm trying to put in column some of the value i have in line.

Here is an example of what i have on my table

And i would like to change the way we display data like this

I tried the function PIVOT like this

SELECT
*
FROM (
SELECT
CONCAT('New_Description',SAFE_CAST(ROW_NUMBER() OVER (ORDER BY Name) AS STRING)) AS number,
*
FROM (
SELECT
DISTINCT Name,
Country,
Type of sale
Description
FROM
sales_2022
PIVOT (MAX(Description) FOR number IN ('New_Description1',
'New_Description2'))

The problem is the query is creating only 2 column (what i expect) but add NULL value for the Country UK because it seems that we need to add New_Description3 ... if we want to pivot.

Any idea how we could just fulfill in 2 column max ?

Thanks a lot

And here is the format expected

Welcome

Please post your data not as an image but something usable that we can emulate on our server gia ddl and dml

--ddl

Create table sample(name varchar(50), typeofsale varchsr(50),
--etc)

Insert into sample
Value('zazu','top of the line',-etc)

Help us help you

Hello Yosiasz,

Sorry for that, i copy past below the database i have today (sample)

Name Country Type of sale Title Subtitle Description
Book France Bookstore Harry Potter FR Livre 1 Version Classique
Book France Bookstore Harry Potter FR Livre 1 Collector Version
Book France Bookstore Harry Potter FR Livre 2 Version Classique
Book France Bookstore Harry Potter FR Livre 2 Collector Version
Book France Supermarket Harry Potter FR Livre 1 Version Classique
Book France Supermarket Harry Potter FR Livre 1 Collector Version
Book France Supermarket Harry Potter FR Livre 2 Version Classique
Book France Supermarket Harry Potter FR Livre 2 Collector Version
Book France Online Harry Potter FR Livre 1 Version Classique
Book France Online Harry Potter FR Livre 1 Collector Version
Book France Online Harry Potter FR Livre 2 Version Classique
Book France Online Harry Potter FR Livre 2 Collector Version
Book France Fair Harry Potter FR Livre 1 Version Classique
Book France Fair Harry Potter FR Livre 1 Collector Version
Book France Fair Harry Potter FR Livre 2 Version Classique
Book France Fair Harry Potter FR Livre 2 Collector Version
Book UK Bookstore Harry Potter GB Book 1 Version Collector
Book UK Bookstore Harry Potter GB Book 1 Classical version
Book UK Bookstore Harry Potter GB Book 2 Version Collector
Book UK Bookstore Harry Potter GB Book 2 Classical version
Book UK Supermarket Harry Potter GB Book 1 Version Collector
Book UK Supermarket Harry Potter GB Book 1 Classical version
Book UK Supermarket Harry Potter GB Book 2 Version Collector
Book UK Supermarket Harry Potter GB Book 2 Classical version
Book UK Online Harry Potter GB Book 1 Version Collector
Book UK Online Harry Potter GB Book 1 Classical version
Book UK Online Harry Potter GB Book 2 Version Collector
Book UK Online Harry Potter GB Book 2 Classical version
Book UK Fair Harry Potter GB Book 1 Version Collector
Book UK Fair Harry Potter GB Book 1 Classical version
Book UK Fair Harry Potter GB Book 2 Version Collector
Book UK Fair Harry Potter GB Book 2 Classical version

And the expected new table after using PIVOT function

Name Country Type of sale Title Subtitle Subtile 2 Description 1 Description 2
Book France Bookstore Harry Potter FR Livre 1 Livre 2 Version Classique Version Collector
Book France Supermarket Harry Potter FR Livre 1 Livre 2 Version Classique Version Collector
Book France Online Harry Potter FR Livre 1 Livre 2 Version Classique Version Collector
Book France Fair Harry Potter FR Livre 1 Livre 2 Version Classique Version Collector
Book UK Bookstore Harry Potter GB Book 1 Book 2 Classical version Collector Version
Book UK Supermarket Harry Potter GB Book 1 Book 2 Classical version Collector Version
Book UK Online Harry Potter GB Book 1 Book 2 Classical version Collector Version
Book UK Fair Harry Potter GB Book 1 Book 2 Classical version Collector Version

Thanks a lot for your strong help

please repost your sample data as DDL and DML

create table #sample -- with column names and data types

insert into #sample
values('x','y'),()

Using the Text to DDL tab at http://sqlfiddle.com to generate consumable test data:

CREATE TABLE #t
    ("Name" varchar(4), "Country" varchar(6), "Type of sale" varchar(11), "Title" varchar(15), "Subtitle" varchar(7), "Description" varchar(17))
;

INSERT INTO #t
    ("Name", "Country", "Type of sale", "Title", "Subtitle", "Description")
VALUES
    ('Book', 'France', 'Bookstore', 'Harry Potter FR', 'Livre 1', 'Version Classique'),
    ('Book', 'France', 'Bookstore', 'Harry Potter FR', 'Livre 1', 'Collector Version'),
    ('Book', 'France', 'Bookstore', 'Harry Potter FR', 'Livre 2', 'Version Classique'),
    ('Book', 'France', 'Bookstore', 'Harry Potter FR', 'Livre 2', 'Collector Version'),
    ('Book', 'France', 'Supermarket', 'Harry Potter FR', 'Livre 1', 'Version Classique'),
    ('Book', 'France', 'Supermarket', 'Harry Potter FR', 'Livre 1', 'Collector Version'),
    ('Book', 'France', 'Supermarket', 'Harry Potter FR', 'Livre 2', 'Version Classique'),
    ('Book', 'France', 'Supermarket', 'Harry Potter FR', 'Livre 2', 'Collector Version'),
    ('Book', 'France', 'Online', 'Harry Potter FR', 'Livre 1', 'Version Classique'),
    ('Book', 'France', 'Online', 'Harry Potter FR', 'Livre 1', 'Collector Version'),
    ('Book', 'France', 'Online', 'Harry Potter FR', 'Livre 2', 'Version Classique'),
    ('Book', 'France', 'Online', 'Harry Potter FR', 'Livre 2', 'Collector Version'),
    ('Book', 'France', 'Fair', 'Harry Potter FR', 'Livre 1', 'Version Classique'),
    ('Book', 'France', 'Fair', 'Harry Potter FR', 'Livre 1', 'Collector Version'),
    ('Book', 'France', 'Fair', 'Harry Potter FR', 'Livre 2', 'Version Classique'),
    ('Book', 'France', 'Fair', 'Harry Potter FR', 'Livre 2', 'Collector Version'),
    ('Book', 'UK', 'Bookstore', 'Harry Potter GB', 'Book 1', 'Version Collector'),
    ('Book', 'UK', 'Bookstore', 'Harry Potter GB', 'Book 1', 'Classical version'),
    ('Book', 'UK', 'Bookstore', 'Harry Potter GB', 'Book 2', 'Version Collector'),
    ('Book', 'UK', 'Bookstore', 'Harry Potter GB', 'Book 2', 'Classical version'),
    ('Book', 'UK', 'Supermarket', 'Harry Potter GB', 'Book 1', 'Version Collector'),
    ('Book', 'UK', 'Supermarket', 'Harry Potter GB', 'Book 1', 'Classical version'),
    ('Book', 'UK', 'Supermarket', 'Harry Potter GB', 'Book 2', 'Version Collector'),
    ('Book', 'UK', 'Supermarket', 'Harry Potter GB', 'Book 2', 'Classical version'),
    ('Book', 'UK', 'Online', 'Harry Potter GB', 'Book 1', 'Version Collector'),
    ('Book', 'UK', 'Online', 'Harry Potter GB', 'Book 1', 'Classical version'),
    ('Book', 'UK', 'Online', 'Harry Potter GB', 'Book 2', 'Version Collector'),
    ('Book', 'UK', 'Online', 'Harry Potter GB', 'Book 2', 'Classical version'),
    ('Book', 'UK', 'Fair', 'Harry Potter GB', 'Book 1', 'Version Collector'),
    ('Book', 'UK', 'Fair', 'Harry Potter GB', 'Book 1', 'Classical version'),
    ('Book', 'UK', 'Fair', 'Harry Potter GB', 'Book 2', 'Version Collector'),
    ('Book', 'UK', 'Fair', 'Harry Potter GB', 'Book 2', 'Classical version')
;

Your data does not correspond to the results (Collector Version/Version Collector) but something like the following may get you started:

WITH OrderNums
AS
(
	SELECT [Name], Country, [Type of sale], Title, Subtitle, [Description]
		,DENSE_RANK() OVER (PARTITION BY [Name], Country, [Type of sale], Title ORDER BY Subtitle) AS SubTitleNum
		,ROW_NUMBER() OVER (PARTITION BY [Name], Country, [Type of sale], Title, Subtitle ORDER BY [Description]) AS DescNum
	FROM #t
)
SELECT [Name], Country, [Type of sale], Title
	,MAX(CASE WHEN SubTitleNum = 1 THEN Subtitle ELSE '' END) AS Subtitle1
	,MAX(CASE WHEN SubTitleNum = 2 THEN Subtitle ELSE '' END) AS Subtitle2
	,MAX(CASE WHEN DescNum = 1 THEN [Description] ELSE '' END) AS Description1
	,MAX(CASE WHEN DescNum = 2 THEN [Description] ELSE '' END) AS Description2
FROM OrderNums
GROUP BY [Name], Country, [Type of sale], Title;
1 Like