Value Function

1

I am doing some extra training for work I am looking to roll up information in a single row

ID,EMail1, Email2, Email,...out to Email 10

There is no specific data set, we can make it what ever we want. There needs to be duplicates in the email fields. The task is to find the number of duplicates in that row with the header being the email address and the count of duplicates

ID|Email@aol.com|Email@gmail.com|....
1|3|4|...

They want me to use the function "Value" but cannot find anything about the syntax.
My manager said it should be a very simple query, but without any reference material it is hard to put it together.

Any help would appreciated.

2

Why would ur manager mandate what function to use? Sounds more like homework, but look into dynamic pivot tables. If you provide some ddl, sample data and expected results, we could help with a query

1 Like
3

Mike,
I wish I could. HE gave us the task as stated, no data, no context.
I created a sample of 10 records, but going crazy to understand the function.

That is why I am looking for any example of syntax on it to help he get started.

Thanks

4

I'm not 100% sure even what you're trying to do.

There's no "value" function in SQL Server. Are you using SQL Server or some other dbms?

There's a VALUES clause, which could conceivably be used in that type of query, although it's probably not the best/easiest way to do it.

5

Thanks, Is there a way without the "value" function?

6

You can try dynamic pivot. here's some sample data and code, but no idea if it works for your scenario

drop table if exists #t

Create table #t (id int, EmailAddress varchar(200))

insert into #t 
values
(1, 'email@aol.com'),
(1, 'email1@aol.com'),
(1, 'email2@aol.com'),
(1, 'email2@aol.com'),
(2, 'email@aol.com'),
(2, 'email2@aol.com'),
(2, 'email3@aol.com'),
(3, 'email4@aol.com'),
(4, 'email5@aol.com'),
(5, 'email@aol.com')

DECLARE @Colslist VARCHAR(MAX)  
DECLARE @Cols TABLE (Head VARCHAR(MAX))  
  
INSERT @Cols (Head)  
SELECT DISTINCT EmailAddress
FROM #t
  
SELECT @ColsList = COALESCE(@ColsList + ',[', '[') + Head + ']'  
FROM @Cols t  
 
EXEC ('SELECT *  
FROM   
(  
        SELECT a.ID, EmailAddress
        FROM #t a 
) t  
PIVOT (count(EmailAddress) FOR EmailAddress IN (' + @ColsList + ')) PVT')
7

Mike,
Thanks..that works on a flat file.
What I am looking for is the following, I created the sample and the 2 results(he added one this morning)

Create table #t2 (id int,EmailAddress1 varchar(50),EmailAddress2 varchar(50),EmailAddress3 varchar(50),EmailAddress4 varchar(50),EmailAddress5 varchar(50),EmailAddress6 varchar(50),EmailAddress7 varchar(50),EmailAddress8 varchar(50),EmailAddress9 varchar(50),EmailAddress10 varchar(50))
insert into #t2
values
(1,'email1@aol.com','email2@aol.com','email1@aol.com','email2@aol.com','email1@aol.com','email3@aol.com','email1@aol.com','email3@aol.com','email1@aol.com','email3@aol.com'),
(2,'email1@aol.com','email1@aol.com','email1@aol.com','email2@aol.com','email2@aol.com','email3@aol.com','email3@aol.com','email3@aol.com','email3@aol.com','email2@aol.com'),
(3,'email3@aol.com','email3@aol.com','email2@aol.com','email2@aol.com','email3@aol.com','email3@aol.com','email2@aol.com','email1@aol.com','email1@aol.com','email1@aol.com'),
(4,'email1@aol.com','email2@aol.com','email3@aol.com','email3@aol.com','email2@aol.com','email1@aol.com','email2@aol.com','email2@aol.com','email1@aol.com','email3@aol.com'),
(5,'email2@aol.com','email3@aol.com','email1@aol.com','email1@aol.com','email3@aol.com','email1@aol.com','email2@aol.com','email2@aol.com','email3@aol.com','email3@aol.com'),
(6,'email3@aol.com','email2@aol.com','email2@aol.com','email2@aol.com','email3@aol.com','email1@aol.com','email2@aol.com','email3@aol.com','email1@aol.com','email2@aol.com'),
(7,'email1@aol.com','email1@aol.com','email3@aol.com','email1@aol.com','email1@aol.com','email2@aol.com','email1@aol.com','email2@aol.com','email2@aol.com','email3@aol.com'),
(8,'email2@aol.com','email3@aol.com','email1@aol.com','email3@aol.com','email2@aol.com','email1@aol.com','email1@aol.com','email2@aol.com','email2@aol.com','email2@aol.com'),
(9,'email3@aol.com','email2@aol.com','email2@aol.com','email3@aol.com','email1@aol.com','email2@aol.com','email1@aol.com','email3@aol.com','email1@aol.com','email1@aol.com'),
(10,'email2@aol.com','email1@aol.com','email3@aol.com','email1@aol.com','email2@aol.com','email3@aol.com','email2@aol.com','email3@aol.com','email1@aol.com','email2@aol.com')

Part1
ID|Max_Duplicates
1|5
2|4

Part2
ID|email1@aol.com|email2@aol.com|email3@aol.com
1|5|2|3
2|3|3|4

8

Is this a lesson in the evils of unnormalized data? No sane database would be structured like this.

9

It is an exercise in rolling up data. The data set I created is over duplicated.
But he wants a simple query to give the results. WE are told not to expand the data vertically as the first solution did. Me and my team are at a loss.

10

So after the team discussion our manager gave the solution,

Create table #t2 (id int,EmailAddress1), (EmailAddress2), (EmailAddress3), (EmailAddress4), (EmailAddress5), (EmailAddress6), (EmailAddress7), (EmailAddress8), (EmailAddress9), (EmailAddress10 varchar(50))
insert into #t2
values
(1,'email1@aol.com','email2@aol.com','email1@aol.com','email2@aol.com','email1@aol.com','email3@aol.com','email1@aol.com','email3@aol.com','email1@aol.com','email3@aol.com'),
(2,'email1@aol.com','email1@aol.com','email1@aol.com','email2@aol.com','email2@aol.com','email3@aol.com','email3@aol.com','email3@aol.com','email3@aol.com','email2@aol.com'),
(3,'email3@aol.com','email3@aol.com','email2@aol.com','email2@aol.com','email3@aol.com','email3@aol.com','email2@aol.com','email1@aol.com','email1@aol.com','email1@aol.com'),
(4,'email1@aol.com','email2@aol.com','email3@aol.com','email3@aol.com','email2@aol.com','email1@aol.com','email2@aol.com','email2@aol.com','email1@aol.com','email3@aol.com'),
(5,'email2@aol.com','email3@aol.com','email1@aol.com','email1@aol.com','email3@aol.com','email1@aol.com','email2@aol.com','email2@aol.com','email3@aol.com','email3@aol.com'),
(6,'email3@aol.com','email2@aol.com','email2@aol.com','email2@aol.com','email3@aol.com','email1@aol.com','email2@aol.com','email3@aol.com','email1@aol.com','email2@aol.com'),
(7,'email1@aol.com','email1@aol.com','email3@aol.com','email1@aol.com','email1@aol.com','email2@aol.com','email1@aol.com','email2@aol.com','email2@aol.com','email3@aol.com'),
(8,'email2@aol.com','email3@aol.com','email1@aol.com','email3@aol.com','email2@aol.com','email1@aol.com','email1@aol.com','email2@aol.com','email2@aol.com','email2@aol.com'),
(9,'email3@aol.com','email2@aol.com','email2@aol.com','email3@aol.com','email1@aol.com','email2@aol.com','email1@aol.com','email3@aol.com','email1@aol.com','email1@aol.com'),
(10,'email2@aol.com','email1@aol.com','email3@aol.com','email1@aol.com','email2@aol.com','email3@aol.com','email2@aol.com','email3@aol.com','email1@aol.com','email2@aol.com')

SELECT [id], (
SELECT MAX(Counts) FROM (
SELECT COUNT(email) AS Counts FROM (
VALUES
(EmailAddress1, id),
(EmailAddress2, id),
(EmailAddress3, id),
(EmailAddress4, id),
(EmailAddress5, id),
(EmailAddress6, id),
(EmailAddress7, id),
(EmailAddress8, id),
(EmailAddress9, id),
(EmailAddress10, id)
) AS V(email, id)
WHERE email is not null
GROUP BY email
) AS A
) AS Counts
FROM #t2

11

Yes, the VALUES clause, as I stated (technically it's a "table constructor"). There is no "VALUE" "function" in SQL Server.