Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

SQL - How to transfor records into a single records

Posted on 2012-03-14
10
Medium Priority
?
330 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
NFR key for Veeam Agent for Linux

Veeam is happy to provide a free NFR license for one year.  It allows for the non‑production use and valid for five workstations and two servers. Veeam Agent for Linux is a simple backup tool for your Linux installations, both on‑premises and in the public cloud.

 
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

NFR key for Veeam Agent for Linux

Veeam is happy to provide a free NFR license for one year.  It allows for the non‑production use and valid for five workstations and two servers. Veeam Agent for Linux is a simple backup tool for your Linux installations, both on‑premises and in the public cloud.

Question has a verified solution.

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

Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
Sometimes it takes a new vantage point, apart from our everyday security practices, to truly see our Active Directory (AD) vulnerabilities. We get used to implementing the same techniques and checking the same areas for a breach. This pattern can re…
Are you ready to place your question in front of subject-matter experts for more timely responses? With the release of Priority Question, Premium Members, Team Accounts and Qualified Experts can now identify the emergent level of their issue, signal…

609 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