T-SQL (SS2K8) :: Convert Records From Rows To Columns
Nov 13, 2014
I have 5 columns in my database. 1 column is coming like a dynamic.
I want to convert records from rows to columns. Currently I have a data like this.
Race AgeRange Amount
W 17-20 500
W 21-30 400
W 31-40 200
A 17-20 100
H 41-50 250
H 51-60 290
So age range is not fixed and it can be any and I have one separate relational table for age range where it's coming from. Now I want to convert it into columns like
Race 17-20 21-30 31-40 41-50 51-60
W 500 400 200 0 0
A 100 0 0 0 0
H 0 0 0 250 290
View 3 Replies
ADVERTISEMENT
Sep 17, 2014
I work with SQLite and need to write a query the old school way to convert rows to columns. If it was MS SQL I would use pivot to get the expected result. However this is SQLite I cannot use pivot.
Sample data:
create table t1 (id int, Dept char (1), Total int);
insert t1
select 1, 'A', 100
union
select 2, 'B', 120
union
select 3, 'C', 140
union
select 4, 'D', 150;
How do I use LEFT OUTER JOIN to produce result similar to the below?
SELECT 'Total' AS Dept,
[A], [B], [C], [D]
from
[code]....
View 7 Replies
View Related
Apr 25, 2014
I have this query
SELECT
'Type'[Type]
,CASE WHEN code='09' THEN SUM(Amt/100) ELSE 0 END
,CASE WHEN code='10' THEN SUM(Amt/100) ELSE 0 END
,CASE WHEN code='11' THEN SUM(Amt/100) ELSE 0 END
,CASE WHEN code='12' THEN SUM(Amt/100) ELSE 0 END
FROM Table1 WHERE (Code BETWEEN '09' AND '12')
GROUP BY Code
and the output
Column 1 Column 2 Column 3 Column 4
Type 14022731.60 0.00 0.00 0.00
Type 0.00 4749072.19 0.00 0.00
Type 0.00 0.00 149214.04 0.00
Type 0.00 0.00 0.00 792210.10
How can I modify the query to come up with output below,
Column 1 Column 2 Column 3 Column 4
Type 14022731.60 4749072.19 149214.04 792210.10
View 9 Replies
View Related
Nov 13, 2014
I have 5 columns in my database. 1 column is coming like a dynamic.
I want to convert records from rows to columns. Currently I have a data like this.
Race AgeRange Amount
W 17-20 500
W 21-30 400
W 31-40 200
A 17-20 100
H 41-50 250
H 51-60 290
So age range is not fixed and it can be any and I have one separate relational table for age range where it's coming from. Now I want to convert it into columns like
Race 17-20 21-30 31-40 41-50 51-60
W 500 400 200 0 0
A 100 0 0 0 0
H 0 0 0 250 290
View 1 Replies
View Related
Aug 5, 2014
I managed to transpose rows into columns.
;WITH
ctePreAgg AS
(
select top 500 act_reference "ActivityRef",
row_number() over (partition by act_reference order by act_reference) as rowno,
t3.s_initials "Initials"
from mytablestuff
order by act_reference
[code]...
But what I would love to do next is take each of the above rows - and return the initials either in one column with all the nulls and duplicate values removed, separated by a comma ..
ref, initials
Ag-4xYS
Ag-6xYS,BL
Ap-1xKW
At-2x SAS,CW
At-3x SAS,CW
OR the above but using variable number of columns based on the maximum number of different initials for each row.this is not strictly required, but maybe neater for further work on the view
ref, init1,init2
Ag-4xYS
Ag-6xYS,BL
Ap-1xKW
At-2x SAS,CW
At-3x SAS,CW
View 6 Replies
View Related
Nov 14, 2014
I have a scenario wherein one of the column values in a row contains a string value which is non-delimited (as shown below). I need to split them by 2 characters and generate as many rows as count of set of 2 digits in that string.
Current record
RecordID Name CountyList
1 ABC 00
2 CDE 01020304
3 XYZ NULL
Expected output:
RecordID Name CountyList
1 ABC 00
2 CDE 01
2 CDE 02
2 CDE 03
2 CDE 04
3 XYZ NULL
I already have a solution in place to run it thru cursor and then do a while loop on the CountyList column by taking 2 digit value using Substring function (keeping start position dyanamic and jumping 2 positions).
View 2 Replies
View Related
Sep 4, 2014
Scenario is like that single dept can have multiple LocationHeads, If Location heads are multiple then they should display in single column using *starting the name as mentioned bottom under required output.
Below is sample of data:
create table #Temp(depID int, Name varchar(50),LocationHead varchar(50))
insert into #temp values(1,'test','head1')
insert into #temp values(1,'test','head2')
insert into #temp values(1,'test','head3')
insert into #temp values(2,'test1','head1')
insert into #temp values(2,'test1','head2')
Required output
depID Name LocationHead
1test *head1,*head2,*head3
2test1 *head1,*head2
View 2 Replies
View Related
Aug 21, 2014
I've a table like below
ColA ColB
A12
B22
C45
A56
A34
B23
and I need to convert the rows into column names like below
ABCA1A2B1
122245563423
View 1 Replies
View Related
May 29, 2007
how to convert rows into columns.
Data in table
empcode basic
1 1000
2 2000
Required output
empcode 1 2
basic 1000 2000
View 3 Replies
View Related
Nov 13, 2007
Hi there
Assuming I have the following data where the header represents budget cost and the value represent no. of projects
<2K 2K-5K >5K
--------------------
10 15 5
For the above table: the following is my sql:
SELECT
SUM (CASE WHEN PRJ.BDGT_CST_TOTAL<2000000 THEN 1 ELSE 0 END) <2K,
SUM (CASE WHEN PRJ.BDGT_CST_TOTAL>=2000000 AND PRJ.BDGT_CST_TOTAL<5000000 THEN 1 ELSE 0 END) 2K-5K,
SUM (CASE WHEN PRJ.BDGT_CST_TOTAL>=5000000 THEN 1 ElSE 0 END) >5K
FROM
PRJ_PROJECTS AS PRJ
How do I program it in sql such that the data will be displayed as below? Thanks
Budget_Range No_of_prj
-----------------------
<2K 10
2K-5K 15
>5K 5
View 5 Replies
View Related
Jul 23, 2005
Hi All,I need to help with converting rows to columns in SQL2k.Input:Id Name Role58Ron Doe Associate58Mark BonasDoctor59Mike JohnsonDoctor59John SmithAssociate102Chris CarterAssociate102Ron Doe Associate102James JonesAssociateOutput should look like:IdDoctorAssoc1Assoc2Assoc358Mark BonasRon Doe NULLNULL59Mike JohnsonJohn SmithNULLNULL102NULLChris CarterRon Doe James JonesThere could be more than 3 associates in the input but I only need 3above columns for associates.I used following query:SELECT Q.sales_id,doctor2= (SELECT Q2.name FROM view1 Q2 where Q2.role = 'doctor'and Q2.sales_id = Q.sales_id),assoc1= (SELECT Q2.name FROM view1 Q2 where Q2.role ='associate' and Q2.sales_id = Q.sales_id),assoc2= (SELECT Q2.name FROM view1 Q2 where Q2.role ='associate' and Q2.sales_id = Q.sales_id),assoc3= (SELECT Q2.name FROM view1 Q2 where Q2.role ='associate' and Q2.sales_id = Q.sales_id)FROM view1 QGROUP BY sales_idand I get this error "Subquery returned more than 1 value" since thereare multiple associate for Id 102.Thenks
View 3 Replies
View Related
Apr 24, 2006
hi
i have a sample data like
patientno visit experimentdate
101 1 23-Dec-2004
101 2 23-Mar-2005
101 3 23-Jul-2005
102 1 23-Dec-2004
102 2 23-Mar-2005
102 3 23-Jul-2005
i want it to display like this
patientno visit1 visit1date visit2 visit2date visit3 visit3date
101 1 23-dec-2004 2 23-Mar-2005 3 23-Jul-2005
102 1 23-dec-2004 2 23-Mar-2005 3 23-Jul-2005
..please suggest how can we do it
View 4 Replies
View Related
Apr 10, 2008
Hi,
I have a table which consists of some rows
i want to get rows as columns
For eg:
table1
id name dept
1 x a
2 y b
3 z c
i want to write a select query to fetch result as
1 2 3
x y z
a b c
can anyone help please
View 10 Replies
View Related
Oct 22, 2014
I am fairly new to SQL and writing queries so bear with my faults. I am learning on the job, which is good and bad. Below is a query that I have written to obtain some information. The problem arises when we have a patient who goes from Patient Type '1' to Patient Type '2'. This needs to be considered a singular visit and the only way I can think that this may work is if: for any specific medical record a dsch_ts is equal to the Admit TS on the next row.
How to complete something like this and my google searches have been fruitless. I attached a spreadsheet with an example of what I am getting.
SELECT DISTINCT
TPM300_PAT_VISIT.med_rec_no,
TSM040_PERSON_HDR.lst_nm AS 'Last Name',
TSM040_PERSON_HDR.fst_nm AS 'First Name',
[Code] ....
View 6 Replies
View Related
Apr 3, 2008
Hello All,
I have one table with 9 different columns for e-mail address like mail_address1, mail_address2 etc....
now i want to insert serial no 1 for mail_address1, serial no 2 for mail_address2 like wise for a particular id.
Current Format :-
code mail_address1 mail_address2 mail_address3
1111 abc@yahoo.com xyz@yahoo.com null
Required format:-
code sr_no mail_address
1111 1 abc@yahoo.com
1111 2 xyz@yahoo.com
1111 3 null
i hope that i am clear with my question.
Can any one help me?
Thanks
Prashant
View 4 Replies
View Related
Jan 13, 2008
Hi,
I have a table with 5 columns, from that I need only 3 columns , but the columns should be rows in the output
Ex Table 1
col1 col2 col3
Now I want col1 as one row n col2 as 2nd row and col3 as 3rd row
What query should I write to get this?
Will union work
Thank You
View 1 Replies
View Related
Oct 8, 2015
How can I convert the following data
Create Table tbl_Customer_Bank_Details
(
[CustomerID] int,
[CustomerName] varchar(50),
[AccountType] varchar(50),
[BankAccountNo] varchar(50)
)
[Code] ...
into the following format
Create Table tbl_Customer_Bank_Details2
(
[CustomerID] int,
[CustomerName] varchar(50),
[AccountType] varchar(50),
[BankAccountNo1] varchar(50),
[Code] ....
I am looking at this [URL]
how to do this in SQL 2000.
View 16 Replies
View Related
Apr 24, 2015
i have a table with dob and test results , i am trying to pull the data from the table and converting rows columns , below is the table i am using . i used to pivot to do this .
create table #TEST_RESULTS
(ID INT,NAME VARCHAR(10),DOB DATETIME,DAYS_SINCE_BIRTH_TO_TEST INT,TEST_RESULTS INT )
INSERT INTO #TEST_RESULTS
VALUES(1,'A','2015-01-01' , 0 ,1)
,(1,'A','2015-01-01' , 0 ,1)
,(1,'A','2015-01-01' , 1 ,3)
,(1,'A','2015-01-01' , 2 ,6)
[code]...
View 9 Replies
View Related
Apr 30, 2008
Hi I have a 'settings' table similar to:
* Setting_ID
* SettingGroup
* SettingSubGroup
* SettingKey
* SettingValue
example data:
1, Comms, Com1, Speed, 9600
2, Comms, Com1, Parity, N
3, Comms, Com1, DataBits, 8
4, Comms, Com1, StopBits, 1
5, Comms, Com2, Speed, 2400
6, Comms, Com2, Parity, E
7, Comms, Com2, DataBits, 7
8, Comms, Com2, StopBits, 2
I want to have a query like
SELECT SettingKey, SettingValue FROM Settings WHERE SettingGroup='Comms' GROUP BY SettingSubGroup
where the result set has a single row for each distinct value of SettingGroup. I want the result set to look like:
SettingSubGroup, Speed, Parity, DataBits, StopBits <--column headings
----------------
Com1, 9600, N, 8, 1
Com2, 2400, E, 7, 2
I've had a look at the PIVOT command but it seems to require an aggregate function... is there a way to simply flip from rows to columns?
View 1 Replies
View Related
Jul 17, 2014
Following is my table structure
IDRowCount PagID
1448
2267
3297
4216
5405
6254
[Code] ....
PageId is currently set to 0
I have a user input, @IntNoOfRowsPerPage = 800 Means 800 rows per page. So following is the output I require.
AIDRowCountPageId
1448 1
2267 1
3297 2
4216 2
5405 3
6254 3
[Code] ....
The values of PageID are such that summation of RowCount for PageID is <= @IntNoOfRowsPerPage (i.e, 800)
If NTILE function can be used in such scenarios.
View 5 Replies
View Related
Mar 6, 2014
I have two columns which needs to repeat based on ID and number of distinct rows in that ID.
ID Date Created
1 1/1/2012 Sudheer
1 1/2/2013 Sudheer
1 3/3/2013 Sudheer
2 1/2/2014 Veera
2 2/5/2015 Veera
Results
ID Date Created Date Created Date Created
1 1/1/2012 Sudh 1/2/2013 Sudh 3/3/2013 Sudh
2 1/2/2014 Veera 2/5/2015 Veera
View 3 Replies
View Related
Aug 10, 2015
I have requirement to list each row into column. I have tried with pivot query but unable to get it. I am using SQL Server 2008 database.
Here is the sample data:
CREATE TABLE dbo.test (
action_id numeric,
action VARCHAR(20) NOT NULL,
[Code].....
View 3 Replies
View Related
Jan 3, 2015
rewrite the below two queries (so that i can avoid duplicates) i need to send email to everyone not the dup[right][/right]licated ones)?
Create table #MyPhoneList
(
AccountID int,
EmailWork varchar(50),
EmailHome varchar(50),
EmailOther varchar(50),
[Code] ....
--> In this table AccountID is uniquee
--> email values could be null or repetetive for work / home / Other (same email can be used more than one columns for accountid)
-- a new column will be created with name as Sourceflag( the value could be work, Home, Other depend on email coming from) then removes duplicates
SELECT AccountID , Email, SourceFlag, ROW_NUMBER() OVER(PARTITION BY AccountID, Email ORDER BY Sourceflag desc) AS ROW
INTO #List
from (
SELECTAccountID
, EmailWorkAS EMAIL
, 'Work'AS SourceFlag
FROM#MyPhoneList (NoLock) eml
WHEREIsOffersToWorkEmail= 1
[code]....
View 9 Replies
View Related
Feb 17, 2015
writing SQL code to convert blocks of columns into rows.
Example,
Id A1 A2 B1 B2
1 1 1 1 1
2 2 2 2 2
3 3 3 3 3
into
Id Group Value
1 A 1
1 A 1
1 B 1
1 B 1
2 A 2
2 A 2
2 B 2
2 B 2
View 2 Replies
View Related
Mar 7, 2008
i want to convert the rows of one table to be the column of another table in run time......i have two tables one having student id and name and in another table there are student id, marks and subject as english. hindi and maths in rows...i want to make another a third table which contains student id , name , marks as english, hindi, maths as the column....tried a lot but didnt get the right way...do we have to do it through Join or is there some other technique by which we can solve the query........i dont know wheather i am really informative or not..but am sitting online if incase you wanna know something else even..........
View 4 Replies
View Related
May 14, 2014
I have a table with this info:
NrCard numberPersonAuto123456789101112
11111111111111111111User1VW Jetta6,46,46,46,45,825,825,825,825,825,825,826,4
22222222222222222222User2Honda CR-V 13,2113,2113,2112,0112,0112,0112,0112,0112,0112,0113,2113,21
How I can get this result:
NrCard numberPersonAutomonthvalue
11111111111111111111User1VW Jetta16,4
11111111111111111111User1VW Jetta26,4
11111111111111111111User1VW Jetta36,4
11111111111111111111User1VW Jetta45,82
11111111111111111111User1VW Jetta55,82
11111111111111111111User1VW Jetta65,82
[code]....
Should I use unpivot or pivot?
View 2 Replies
View Related
Aug 27, 2015
i have following table columns and i want to convert these all columns into row
SELECT [CASHINHAND]
,[CASHATBANK]
,[DEPOSITS]
,[ACCRECEVABLE]
,[SUNDRYDEBTORS]
,[LOANANDADVANCES]
[Code] ....
required output looks like
CASHINHAND 118950
CASHATBANK 200
DEPOSITS 3000
ACCRECEIVABLE
25000
View 3 Replies
View Related
Jun 17, 2015
Using the following tables and data---
CREATE TABLE tblRiskReviewHistory(RiskReviewID int, RiskReviewHistoryID int, Name nvarchar(20), Description nvarchar(50), Date date)
INSERT tblRiskReviewHistory(RiskReviewID, RiskReviewHistoryID, Name, Description, Date)
VALUES(1,1,'Customer A','Profile Assessment','01/01/2015'),
(1,2,'Customer B','Profile Assessment','02/20/2015')
[Code] ...
And currently outputs;
Name Description Date Question Answer
Customer A Profile Assessment 01/01/2015
How complex is the structure?
Customer A
Profile Assessment
01/01/2015
The total value of assets?
Less than GBP 1 million
Customer A
Profile Assessment
01/01/2015
The volume of transactions undertaken?
Low (-1 pmth)
[Code] ....
However, I would like it to output;
Name
Description
Date
How complex is the structure?
The total value of assets?
The volume of transactions undertaken?
How was the client introduced?
Where does the Customer reside?
[Code] ....
The number of questions are unknown for each RiskReviewID and they can be added to in the future.
View 7 Replies
View Related
Apr 14, 2015
I have around 3 tables having around 20 to 30gb of data. My table A related to table B by a FK and same way table B related to table C by FK. I would like to delete all rows satisfying certain condition from table A and all corresponding related records from table B and C. I have created a query to delete the grandchild first, followed by child table and finally parent. I have used inner join in my delete query. As you all know, inner join delete operations, are going to be extremely resource Intensive especially on bigger tables.
What is the best approach to delete all these rows? There are many constraints, triggers on these tables. Also, there might be some FK relations to other tables as well.
View 3 Replies
View Related
Feb 3, 2015
I have this query:
SELECT TOP (100) PERCENT dbo.Filteredfs_franchise.fs_franchiseid AS FranchiseId, dbo.Filteredfs_franchise.fs_brandidname AS Brand,
dbo.Filteredfs_franchise.fs_franchisetypename AS [Franchise Type], dbo.Filteredfs_franchise.fs_franchisenumber AS [Franchise Number],
dbo.Filteredfs_franchise.fs_transactiontypename AS [Transaction Type], dbo.Filteredfs_franchise.fs_franchisestatusname AS [Status Code],
[Code] ....
I need to pivot this so I can get one row per franchiseID and multiple columns for [Franchisee Name Entity] and [Franchise Name Individual]. Each [Franchisee Name Entity] and [Franchise Name Individual] has associated percentage of ownership.
This has to be dynamic, because each FranchiseID can have anywhere from 1 to 12 respective owners and those can be any combination of of Entity and Individual. Please, see the attached example for Franchise Number 129 (that one would have 6 additional columns because there are 3 Individual owners with 1 respective Percentage of ownership).
The question is how do I PIVOT and preserve the percentage of ownership?
View 3 Replies
View Related
Apr 21, 2015
I have a table with single row like below
_ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _
Column0 | Column1 | Column2 | Column3 | Column4|
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
Value0 | Value1 | Value2 | Value3 | Value4 |
Am looking for a query to convert above table data to multiple rows having column name and its value in each row as shown below
_ _ _ _ _ _ _ _
Column0 | Value0
_ _ _ _ _ _ _ _
Column1 | Value1
_ _ _ _ _ _ _ _
Column2 | Value2
_ _ _ _ _ _ _ _
Column3 | Value3
_ _ _ _ _ _ _ _
Column4 | Value4
_ _ _ _ _ _ _ _
View 6 Replies
View Related
Oct 5, 2015
I have a table with 3 columns (ID Int , Name Varchar(25), Course Varchar(20))
My source data looks like below
ID Name Course
1 A Java
1 A C++
2 B Java
2 B SQL Server
2 B .Net
2 B SAP
3 C Oracle
My Output should look like below...
ID Name Course(1) Course(2) Course(3) Course(4)
1 A Java C++
2 B Java SQL Server .Net SAP
3 C Oracle
Basically need t-sql to Convert non fixed rows to non fixed columns...
Rule: IF each ID and Name have more than 1 course then show it in new columns as course(1) course(2)..Course(n)
Create SQL:
Create table Sample (ID Int null , Name Varchar(25) null, Course Varchar(20) null)
Insert SQL:
INSERT Sample (ID, Name, Course)
VALUES (1,'A','Java'),
(1,'A','C++'),
(2,'B','Java'),
(2,'B','SQL Server'),
(2,'B','.Net'),
(2,'B','SAP'),
(3,'C','Oracle')
View 12 Replies
View Related
Aug 27, 2014
I'd like to first figure out the count of how many rows are not the Current Edition have the following:
Second I'd like to be able to select the primary key of all the rows involved
Third I'd like to select all the primary keys of just the rows not in the current edition
Not really sure how to describe this without making a dataset
CREATE TABLE [Project].[TestTable1](
[TestTable1_pk] [int] IDENTITY(1,1) NOT NULL,
[Source_ID] [int] NOT NULL,
[Edition_fk] [int] NOT NULL,
[Key1_fk] [int] NOT NULL,
[Key2_fk] [int] NOT NULL,
[Code] .....
Group by fails me because I only want the groups where the Edition_fk don't match...
View 4 Replies
View Related
