[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

SQL - How to transfor records into a single records

Posted on 2012-03-14
10
Medium Priority
?
331 Views
Last Modified: 2012-03-21
I have records of person doing business in difference state, I like to create a single person records with state as column of Boolean value.
for example
I have the following records
Name     | State
----------------------
Person1 | NY
Person1 | WI
Person1 | GA
Person2 | SD
Person2 |WI
Person3 | FL

I like to create record of the following
Name      | NY |WI|MI|SD|GA|FL
----------------------------------------------
Person1  | T   | T  | F  | F | T | F
Person2  | F   | T  | F  | T |  F | F
Person3  | F   | F  | F  | F |  F | T
 
How will I do it?
0
Comment
Question by:tommym121
  • 3
  • 3
  • 2
  • +2
10 Comments
 
LVL 11

Accepted Solution

by:
yuching earned 1200 total points
ID: 37723527
select name,
  max (case when State ='NY' Then 'T' else 'F' end) as NY,
  max(case when State ='WI' Then 'T' else 'F' end) as WI,
  max(case when State ='MI' Then 'T' else 'F' end) as MI,
  max(case when State ='SD' Then 'T' else 'F' end) as SD,
  max(case when State ='GA' Then 'T' else 'F' end) as GA,
  max(case when State ='FL' Then 'T' else 'F' end) as FL
From yourtable
group by name
0
 
LVL 9

Expert Comment

by:sachinpatil10d
ID: 37723591
Try this

    DECLARE @columns VARCHAR(8000)
   DECLARE @columns2 VARCHAR(8000)
   DECLARE @sql NVARCHAR(MAX)
        SET @Columns = SUBSTRING((SELECT DISTINCT ',['+state+']' FROM tbl GROUP BY state FOR XML PATH('')),2,8000)
        SET @Columns2 = SUBSTRING((SELECT DISTINCT ',case when ['+state+'] is null then ''F'' else ''T'' end AS ['+state+']' FROM tbl GROUP BY state FOR XML PATH('')),2,8000)
        SET @SQL = 'SELECT  [Name],' + @Columns2 + ' FROM 
          (Select [Name], state from tbl )  SourceData
        PIVOT
          (min(state) for state in ('+@Columns+')) pivottable 
        '
        EXEC(@sql)

Open in new window

0
 
LVL 20

Expert Comment

by:BuggyCoder
ID: 37723601
you can use pivot:-
here is an example for the same
http://msdn.microsoft.com/en-us/library/ms177410.aspx
0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
LVL 20

Assisted Solution

by:BuggyCoder
BuggyCoder earned 400 total points
ID: 37723699
If you have name of states is fixed, the code below would do, however if state values can change in future, the solution given by @sachinpatil10d is good enough as it builds the case statement and pivot statement:-

SELECT  [Name],
case when [NY] is null then 'F' else 'T' end AS [NY],
case when [NY1] is null then 'F' else 'T' end AS [NY1],
case when [NY2] is null then 'F' else 'T' end AS [NY2],
case when [NY3] is null then 'F' else 'T' end AS [NY3],
case when [NY4] is null then 'F' else 'T' end AS [NY4],
case when [NY5] is null then 'F' else 'T' end AS [NY5] 
FROM (Select [Name], state from <yourtable> )  SourceData
PIVOT
(min(state) for state in ([NY],[NY1],[NY2],[NY3],[NY4],[NY5])) finaltable

Open in new window

0
 

Author Comment

by:tommym121
ID: 37724477
yuching,

what I find is if I have more than Name and State as columns,  SQL server will complain with this  error
Msg 8120, Level 16, State 1, Line 1
Column 'dbo.table_1' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

The select statement is as follows
select name, address,
  max (case when State ='NY' Then 'T' else 'F' end) as NY,
  max(case when State ='WI' Then 'T' else 'F' end) as WI,
  max(case when State ='MI' Then 'T' else 'F' end) as MI,
  max(case when State ='SD' Then 'T' else 'F' end) as SD,
  max(case when State ='GA' Then 'T' else 'F' end) as GA,
  max(case when State ='FL' Then 'T' else 'F' end) as FL
From table_1
group by name

What can I do to correct the error?
0
 
LVL 70

Assisted Solution

by:Scott Pletcher
Scott Pletcher earned 400 total points
ID: 37724982
SELECT
    Name, Address, /*...,*/
    COALESCE([NY], 'F') AS NY, COALESCE([WI], 'F') AS WI, COALESCE([SD], 'F') AS SD,
    COALESCE([GA], 'F') AS GA, COALESCE([FL], 'F') AS FL
FROM (
    SELECT Name, Address, /*...,*/
        [NY] AS NY, [WI] AS WI, [SD] AS SD,
        [GA] AS GA, [FL] AS FL
    FROM (
        SELECT name, address, /*...,*/
            state, 'T' AS state_flag
        FROM table_1
    ) AS base_table
    PIVOT
    (
       MAX(state_flag)
       FOR state IN
       ( [NY], [WI], [SD], [GA], [FL] )
) AS pvt
) AS derived
0
 
LVL 11

Assisted Solution

by:yuching
yuching earned 1200 total points
ID: 37729319
Hi Tom, it depends what your result need to be produced.
If you add a column in the select statement, need to group by that column as well.

example query
select name, address,
  max (case when State ='NY' Then 'T' else 'F' end) as NY,
  ...
From yourtable
group by name, col1


Name     | State  |address
-----------------------------------------
Person1 | NY      | 1
Person1 | WI      | 2
Person1 | GA     |1

Result will be as below
Name      |address | NY |WI|MI|SD|GA|FL
----------------------------------------------
Person1  | 1     |T   | T  | F  | F | T | F
Person1  | 2     |T   | T  | F  | F | T | F
0
 

Author Comment

by:tommym121
ID: 37742050
yuching
what is the max() do?
0
 
LVL 11

Assisted Solution

by:yuching
yuching earned 1200 total points
ID: 37742500
The max select the max result out
Can be  illustrated as below

Your table
Name     | State  |address
-----------------------------------------
Person1 | NY      | 1
Person1 | WI      | 2
Person1 | GA     |1

a. If you did not put a max and group by Name and address.
=======================
select name, address,
 case when State ='NY' Then 'T' else 'F' end as NY,
  ...
From yourtable

Result will be as below
Name      |address | NY |WI|MI|SD|GA|FL
----------------------------------------------
Person1  | 1          |T   | F  | F  | F | F | F  -- first row
Person1  | 2          |F   | T  | F  | F | F| F  -- 2nd row
Person1  | 1          |F   | F  | F  | F | T| F  -- 3rd row

The result will be row by row

b. If you put a max and group by Name and Address
=======================
select name, address,
 max(case when State ='NY' Then 'T' else 'F' end) as NY,
  ...
From yourtable
group by name, address


Result will be as below
Name      |address | NY |WI|MI|SD|GA|FL
----------------------------------------------
Person1  | 1          |T   | F  | F  | F | T | F  -- this is combination of row1 and row3
Person1  | 2          |F   | T  | F  | F | F | F  -- row2

c. If you put a max and group by only name
=======================
select name,
  max(address) as address,
 max(case when State ='NY' Then 'T' else 'F' end) as NY,
  ...
From yourtable
group by name

Result will be as below
Name      |address | NY |WI|MI|SD|GA|FL
----------------------------------------------
Person1  | 2          |T   | T  | F  | F | T | F  
-- this is combination of row1 2, 3, max address =2 will be selected
0
 

Author Closing Comment

by:tommym121
ID: 37746805
Thanks.

yuching,  thanks for the extra explanation of max()
0

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This post looks at MongoDB and MySQL, and covers high-level MongoDB strengths, weaknesses, features, and uses from the perspective of an SQL user.
In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
This Micro Tutorial will teach you how to add a cinematic look to any film or video out there. There are very few simple steps that you will follow to do so. This will be demonstrated using Adobe Premiere Pro CS6.
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…

872 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question