PIVOT Function for Text

[pesirjacq]

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

[pesirjacq]

And here is the format expected

[yosiasz]

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

[pesirjacq]

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

[yosiasz]

please repost your sample data as DDL and DML

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

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

[Ifor]

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;