SQL Tutorial - PIVOT with Text

Another video brought to you by BeardedDev, bringing you tutorials on Data Engineering, Business Intelligence, T-SQL Programming and Data Analysis.

In this SQL tutorial we take a look how to perform operations with character string data types; char or varchar. You can follow along as I create a generic table in SQL Server and then demonstrate the different aggregate functions that can be used with text data types.

I also demonstrate the downfall of PIVOT in that we can't use a subquery to define our new column names.

I demonstrate how PIVOTing works with text data types by performing both a one-to-one PIVOT in which we simply transfer data from rows to columns and a many-to-one PIVOT in which the aggregate function plays an important role on what data is returned.

I also discuss how working with MIN and MAX works with Text data types, it is different to working with Numeric data types.

If you would like to follow along with this SQL tutorial then you can use the below SQL to create the necessary objects.

Please feel free to post any comments.

DROP TABLE IF EXISTS dbo.HouseAttributes;

GO

CREATE TABLE dbo.HouseAttributes
(
HouseId INT NOT NULL,
Attribute VARCHAR(50) NOT NULL,
[Value] VARCHAR(50) NOT NULL
)

INSERT INTO dbo.HouseAttributes (HouseId, Attribute, [Value])
VALUES
(1, 'Master Bedroom Size', '5x5'),
(1, 'Master Bedroom Size', '100x100'),
(1, 'Master Bedroom EnSuite', '1'),
(1, 'Second Bedroom Size', '3.5x4'),
(1, 'Second Bedroom EnSuite', '1'),
(1, 'Third Bedroom Size', '3.2x3.8'),
(1, 'Fourth Bedroom Size', '3.5x3'),
(2, 'Master Bedroom Size', '3.5x4'),
(2, 'Master Bedroom EnSuite', '1'),
(2, 'Second Bedroom Size', '3.5x3.2'),
(3, 'Master Bedroom Size', '4x3.2'),
(3, 'Second Bedroom Size', '3.5x4'),
(3, 'Third Bedroom Size', '3.2x3.8');

SELECT
HouseId,
[Master Bedroom Size],
[Master Bedroom EnSuite],
[Second Bedroom Size],
[Second Bedroom EnSuite],
[Third Bedroom Size],
[Fourth Bedroom Size]
FROM
(
SELECT
HouseId,
Attribute,
[Value]
FROM dbo.HouseAttributes
) AS D
PIVOT
(
MAX([Value]) FOR [Attribute] IN
(
[Master Bedroom Size], [Master Bedroom EnSuite],
[Second Bedroom Size], [Second Bedroom EnSuite],
[Third Bedroom Size], [Fourth Bedroom Size]
)
) AS P;

[bustermika]

I have been searching for this for quite sometime, but all the clips I found did not explained it clearly. But yours is easy to understand thank you so much this helped me alot!

[ViktorParkhomets]

The best SQL tutorials of what I found so far

[ktt]

Thank you! You are a life saver sparing me headaches.

[raymondjurado]

You're pretty good.
Looking forward to dynamic pivoting as in my context the number of elements varies and will be enumerated by a window function.

[ameobiamicrobiological]

Thanks so much for this, just so know you, I watched all the horrible commercials so you would get a small revenue from this.😁

[ViktorParkhomets]

Is it possible to concatenate values, like '5x5; 100x100' ?