Solved

SQL - How to transfor records into a single records

Posted on 2012-03-14
10
314 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 300 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
 
LVL 20

Assisted Solution

by:BuggyCoder
BuggyCoder earned 100 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
6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

 
LVL 69

Assisted Solution

by:ScottPletcher
ScottPletcher earned 100 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 300 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 300 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

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
How to leverage one TLS certificate to encrypt Microsoft SQL traffic and Remote Desktop Services, versus creating multiple tickets for the same server.
Get a first impression of how PRTG looks and learn how it works.   This video is a short introduction to PRTG, as an initial overview or as a quick start for new PRTG users.
This video gives you a great overview about bandwidth monitoring with SNMP and WMI with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're looking for how to monitor bandwidth using netflow or packet s…

760 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

Need Help in Real-Time?

Connect with top rated Experts

22 Experts available now in Live!

Get 1:1 Help Now