Solved

SQL - How to transfor records into a single records

Posted on 2012-03-14
10
321 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
Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

 
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
 
LVL 69

Assisted Solution

by:Scott Pletcher
Scott Pletcher 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

Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

Question has a verified solution.

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

Suggested Solutions

In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Composite queries are used to retrieve the results from joining multiple queries after applying any filters. UNION, INTERSECT, MINUS, and UNION ALL are some of the operators used to get certain desired results.​
This Micro Tutorial will give you a basic overview how to record your screen with Microsoft Expression Encoder. This program is still free and open for the public to download. This will be demonstrated using Microsoft Expression Encoder 4.
This video shows how to quickly and easily add an email signature for all users on Exchange 2016. The resulting signature is applied on a server level by Exchange Online. The email signature template has been downloaded from: www.mail-signatures…

777 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