SQL SERVER - Create Table by matching rows and columns and put Y or N

Question

I currently have a table with all the data, using this data i want to create another table which could be used for auditing purpose (similar layout like pivot table)

Example Below:

Raw Data

Name         Places Visited
----------------------------
Will         London

John         Toronto

Dave         New York

Will         London

What I want (similar to Pivot Table but I can use letters):

Name/Places Visted    London      Toronto      New York

Will                    Y           N             Y

John                    N           Y             N

Dave                    N           N             Y

Any help to produce this will be much appreciated!

I hope i've explained myself well but let me know if you would like to clarify anything.

Many Thanks!!

Why the close vote? The question seems very clear to me. He wants an SQL query that reads from a table containing the "raw data" and gives his desired output. — Mark Byers, Sep 30 '11 at 11:30
@Mo.: Maybe take a look at PIVOT (assumes SQL Server 2005 or newer): http://msdn.microsoft.com/en-us/library/ms177410.aspx — Mark Byers, Sep 30 '11 at 11:33
I've tried it in Excel but couldnt think of a way to do in SQL, I'm quite a beginner in SQL. Using the PIVOT, will it allow me to use my own letters e.g. Y OR N? thank you!! — K-M, Sep 30 '11 at 11:37

Bogdan Sahlean · Accepted Answer · 2011-09-30T12:05:15.623

3

Solution for SQL Server 2005/2008:

DECLARE @Temp TABLE 
(
     Name NVARCHAR(100) NOT NULL
    ,Place NVARCHAR(100) NOT NULL
);

INSERT  @Temp 
SELECT 'Will','London'
UNION ALL
SELECT 'John','Toronto'
UNION ALL
SELECT 'Dave','New York'
UNION ALL
SELECT 'Will','London';

SELECT  
    pvt.Name [Name/Places Visted]
    ,CASE WHEN [London] IS NOT NULL THEN 'Y' ELSE 'N' END [London]
    ,CASE WHEN [Toronto] IS NOT NULL THEN 'Y' ELSE 'N' END [Toronto]
    ,CASE WHEN [New York] IS NOT NULL THEN 'Y' ELSE 'N' END [New York]
FROM    @Temp src
PIVOT( MAX(src.Place) FOR src.Place IN([London], [Toronto], [New York]) ) pvt
ORDER BY pvt.Name DESC;

Results:

Name/Places Visted  London Toronto New York
------------------- ------ ------- --------
Will                Y      N       N
John                N      Y       N
Dave                N      N       Y

Note: Columns resulting from PIVOT operation are static. This means that if you add records with Place='PARIS' you should modify the query like this: PIVOT( MAX(src.Place) FOR src.Place IN([London], [Toronto], [New York], [Paris]). So, you could use PIVOT operator if you have a limited number of cities.

edited Sep 30 '11 at 12:05

answered Sep 30 '11 at 11:59

Bogdan Sahlean

19,233
3
42
57

Thank you very much! will I have to input the names and location individually - i have table with around 200 records? thank you – K-M Sep 30 '11 at 12:08
Doesn't matter how many records you have. What matters is the number of DISTINCT places/cities. – Bogdan Sahlean Sep 30 '11 at 12:50
With unknown number of cities you could use dynamic SQL sentences: http://msdn.microsoft.com/en-us/library/ms188001.aspx – DavidEG Sep 30 '11 at 13:01
[Here](http://stackoverflow.com/questions/7271609/inserting-and-transforming-data-from-sql-table/7276229#7276229) I have presented a solution for a variable number of columns (columns resulted from `PIVOT` operation). – Bogdan Sahlean Sep 30 '11 at 13:06
You should use `COUNT` aggregate function: `SELECT pvt.* FROM @Temp src PIVOT( COUNT(src.Place) FOR src.Place IN([London], [Toronto], [New York]) ) pvt ORDER BY pvt.Name DESC` – Bogdan Sahlean Sep 30 '11 at 14:57

SQL SERVER - Create Table by matching rows and columns and put Y or N

1 Answers1