[Webinar] Streamline your web hosting managementRegister Today

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 334
  • Last Modified:

SQL - How to transfor records into a single records

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
tommym121
Asked:
tommym121
  • 3
  • 3
  • 2
  • +2
5 Solutions
 
yuchingCommented:
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
 
sachinpatil10dCommented:
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
 
BuggyCoderCommented:
you can use pivot:-
here is an example for the same
http://msdn.microsoft.com/en-us/library/ms177410.aspx
0
Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

 
BuggyCoderCommented:
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
 
tommym121Author Commented:
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
 
Scott PletcherSenior DBACommented:
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
 
yuchingCommented:
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
 
tommym121Author Commented:
yuching
what is the max() do?
0
 
yuchingCommented:
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
 
tommym121Author Commented:
Thanks.

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

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

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.

  • 3
  • 3
  • 2
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now