SQL Query To Convert Rows Into Column

Jan 16, 2008

I have detail table like

date item_id grade in out
------ ------- ------- ----- -----
01-01-08 001 A 10 0
02-01-08 001 O 8 0
01-02-08 002 O 1 0
03-01-08 001 T 0 10
02-01-08 003 O 20 0
02-01-08 003 T 0 10
02-01-08 003 B 0 8


Result View
=======

Item_id A B O T Total
------- --- ---- --- --- -------
001 10 0 8 -10 8
002 0 0 1 0 1
003 0 -8 20 -10 2

I want result group by Item_id and sum of grade in column .
where grade could be any Alphabet, the column of result query could varies.
Each grade contains sum of (in-out) of item_id of detail table.

Smartsys99

View 1 Replies


ADVERTISEMENT

Convert The Rows Into Column

Dec 11, 2007

I want to convert the row value as column name
example:-->
value       ratio
3           4.166666666661          1.315789473680          00          0
To :->
value     ratio        ratio1   ratio2   ratio3
3          4.166      1.315    0          0
any ideas?
 
 

View 1 Replies View Related

Convert Rows To Column

May 27, 2008

Hi all,

I have a table in sql server in this format.

ID Color Density Value

1 W 1.5 100
2 X 1.5 100
3 Y 1.5 100
4 Z 1.5 100
5 W 1.6 98
6 X 1.6 98
7 Y 1.6 98
8 Z 1.6 98.1


Now I want to display the data in this format

ID Color Density Value ID Color Density Value ID Color Density Value ID Color Density Value
1 W 1.5 100 2 X 1.5 100 3 Y 1.5 100 4 Z 1.5 100
5 W 1.6 98 6 X 1.6 98 7 Y 1.6 98 8 Z 1.6 98

Basically I want this format so that I can bind this to the datagridview directly and then use the same for a two way binding (saving) back to the database.
How do I get this result format using a query? Thanks in advance.

View 18 Replies View Related

T-SQL (SS2K8) :: Convert Non-delimited Column Value Into Rows

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

Transact SQL :: How To Convert Row To Column With Rows Dynamic

May 21, 2015

I want to convert row to columns with rows dynamic. I have two table, I'll join them and get data. See at

Ok , I try convert rows to columns with PIVOT . i want the result as

GROUP_MOD_IDADDEDITDELETE
G02101

CREATE PROC GET_FUNC @GID CHAR(5)
AS
BEGIN
SELECT FUNCTION_MOD_NAME

[Code] ....

But it's error

Msg 156, Level 15, State 1, Procedure GET_FUNC, Line 13
Incorrect syntax near the keyword 'FOR'.

Note: Type of ALLOW is bit and rows FUNCTION_MOD_NAME is dynamic

View 2 Replies View Related

T-SQL (SS2K8) :: Convert Multiple Rows In Single Column

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

Reporting Services :: Convert Column To Rows In SSRS

Jul 27, 2015

I am having table like below:-

ManagerName, ManagerEmail, Client, P1-Client  ,P1-Number,P1-Description,P2-Client,P2-Number,P1-Description,P3-Client,P3-Number,P3-Description

But I need report below :

ManagerName, ManagerEmail, Client , P1   P1-Client 
                                                                   
P1-Number
                                                                    
P1-Description
                                                            
P2  
                                                                    
P2 -Client 
                                                                   
P2-Number
                                                                    
P2-Description

View 3 Replies View Related

Need Query To Select VarChar Rows That Can Convert To Integer

Jul 23, 2005

I have a table of zip codes, some with Canadian zips. I'd like to takea zip code and search for nearby zips. For example:Dim theZip As Integer = textbox1.text....Parameter.Add(@ziplow, SqlDbType.Int, 5).Value = (theZip - 10)....Parameter.Add(@ziphigh, SqlDbType.Int, 5).Value = (theZip + 10)SELECT * from ZIPCODES where Cast(zip_code as Integer) BETWEEN @lowzipAND @highzipProblem is the letters in the Canadian records cannot be cast asintegers for this process. I get this error:Syntax error converting the varchar value '53151 1' to a column of datatype int.Is there a SQL query that can exclude if no cast can be made to anInteger?Thanks!Anton

View 5 Replies View Related

Sql Query Summerize The Detail Table In Order To Convert Its Trans_type Into Column

Jan 15, 2008

I have detail table like

date item_id grade in out
------------- --------------- ------- -------- ----------
01-01-08 001 A 10 0
02-01-08 001 O 8 0
01-02-08 002 O 1 0
03-01-08 001 T 0 10
02-01-08 003 O 20 0
02-01-08 003 T 0 10
02-01-08 003 B 0 8


Result View
=======

Item_id A B O T Total


001 10 0 8 -10 8
002 0 0 1 0 1
003 0 8 20 -10 18

where grade could be any Alphabet, the column of result query could varies
each grade contain sum of (in-out) of all item_id of detail table.

Smartsys

View 4 Replies View Related

Query To Tranform Rows To Column

Jul 23, 2005

HiYour help for the following query will be highly apprecaited. I'vewasted alot of time on it. Data definition is at the bottom.Thanks-----------------------------------Business need: User selects answers for few questions. Answers arepresented in the form of radio buttons.Questions Table:===================QuestionID QuestionName1 1.Rate your organization2 10.Opportunity in your group3 117.Effectiveness----------------------------------------Answers Table:==============AnswerId AnswerName QuestionID1 1.Best 12 2.Average 13 3.Wrose 14 1.Hardly Any 25 2.not too much 26 3.Average 27 4.Great Deal 28 1.Strong 39 2.Minor 310 3.Nothing 3--------------------------------------------------------Lets say User ABC answered below:For Question 1, she chose '2.Average'For Question 2: she left blankFOr Quesiton 3: she chose '2.Minor'And for user XYZ:For Question 1, she let blankFor Question 2: she left blankFOr Quesiton 3: she chose '3.Nothing'Then the Results table will look like this:Results Table:===============Resultsid QuestionID AnswerId User1 1 2 ABC2 3 9 ABC3 3 10 XYZ---------------------------------------------------Desired Output:===============User 1 10 117--- -- --- ----ABC 2 2XYZ 3Explanation:--> Show ALL distinct users on rows--> show ALL distinct QuestionName on the columns BUT only display thenumber before '.'--> Show the chosen answer (answername) for each user but only displaythe number before '.'-------------------------------------------------------------------if exists (select * from dbo.sysobjects where id =object_id(N'[dbo].[Questions]') and OBJECTPROPERTY(id, N'IsUserTable')= 1)drop table [dbo].[Questions]GOCREATE TABLE [dbo].[Questions] ([QuestionId] [int] IDENTITY (1, 1) NOT NULL ,[QuestionName] [nvarchar] (1000) COLLATESQL_Latin1_General_CP1_CI_AS NULL) ON [PRIMARY]GO-------------------------------if exists (select * from dbo.sysobjects where id =object_id(N'[dbo].[Answers]') and OBJECTPROPERTY(id, N'IsUserTable') =1)drop table [dbo].[Answers]GOCREATE TABLE [dbo].[Answers] ([AnswerId] [int] IDENTITY (1, 1) NOT NULL ,[AnswerName] [nvarchar] (150) COLLATESQL_Latin1_General_CP1_CI_AS NULL ,[QuestionId] [int] NULL) ON [PRIMARY]GO----------------------------------if exists (select * from dbo.sysobjects where id =object_id(N'[dbo].[Results]') and OBJECTPROPERTY(id, N'IsUserTable') =1)drop table [dbo].[Results]GOCREATE TABLE [dbo].[Results] ([ResultId] [int] IDENTITY (1, 1) NOT NULL ,[QuestionId] [int] NULL ,[AnswerId] [int] NULL ,[UserId] [nchar] (40) COLLATE SQL_Latin1_General_CP1_CI_ASNULL) ON [PRIMARY]GO---------------------------------------------INSERT Answers (AnswerName ,QuestionId)VALUES ('1.Best' ,1)goINSERT Answers (AnswerName ,QuestionId)VALUES ('2.Average' ,1)goINSERT Answers (AnswerName ,QuestionId)VALUES ('3.Wrose' ,1)goINSERT Answers (AnswerName ,QuestionId)VALUES ('1.Hardly Any' ,2)goINSERT Answers (AnswerName ,QuestionId)VALUES ('2.not too much' ,2)goINSERT Answers (AnswerName ,QuestionId)VALUES ('3.Average' ,2)goINSERT Answers (AnswerName ,QuestionId)VALUES ('4.Great Deal' ,2)goINSERT Answers (AnswerName ,QuestionId)VALUES ('1.Strong' ,3)goINSERT Answers (AnswerName ,QuestionId)VALUES ('2.Minor' ,3)goINSERT Answers (AnswerName ,QuestionId)VALUES ('3.Nothing' ,3)go-----------------------------INSERT Questions (QuestionName)VALUES ('1.Rate your organization')goINSERT Questions (QuestionName)VALUES ('10.Opportunity in your group')goINSERT Questions (QuestionName)VALUES ('117.Effectiveness')go------------------------------------INSERT Results (QuestionId ,AnswerId ,UserId)VALUES (1 ,2 ,'ABC')goINSERT Results (QuestionId ,AnswerId ,UserId)VALUES (3 ,9 ,'ABC')goINSERT Results (QuestionId ,AnswerId ,UserId)VALUES (3 ,10 ,'XYZ')go

View 2 Replies View Related

Transact SQL :: Query To Convert Single Row Multiple Columns To Multiple Rows

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

SELECT Query (rows Returned In One Column)?

Aug 17, 2005

Hi there,

I¡¦ve got a table with the following as well as other info:

User ID
DirectoryTypeID (int)
Region ID (int)

I need to run a query where I could get the region ID, then, in the second column, I¡¦d get all distinct directory types within that region. For example, if I run the query:

subRegionAreaID directoryTypeID
--------------- ---------------
3 1
3 2
3 3
3 9

If need these results to be:

subRegionAreaID directoryTypeID
--------------- ---------------
3 1, 2, 3, 9

Is this possible?

Many Thanks!! ļ


Grazi

View 1 Replies View Related

Column In Query Results To Number Rows Sequentially

Apr 20, 2015

I need a column in my query results that just numbers the rows sequentially (i.e. 1, 2, 3). How can I do that?

View 6 Replies View Related

How To Get Query Second Column Value/other Rows While Assigning To A Variable In A Stored Procedure?

Feb 6, 2008

Hi,
I'm try to get the query second column value when it is assinged to a varchar variable.
Ex:In SP below is statement I wrote
SET @Values  =  (SELECT COL1,COL2 FROM TABLE)
Question 1:  How to access the COL2 value from @Value2?
Question 2:  How to access the other row values if above returns more than a row?
Please send me the solution as soon as possible.
Thanks-Vikash/Bala

View 3 Replies View Related

Transact SQL :: Selecting Rows As Column Name And Other Column Values As Rows

Jun 25, 2015

I have questions and answers from one table, I need to select questions as column names and answers column values as the results for the questions column.

View 28 Replies View Related

Cast/Convert Mmddyy In String To New DB_DATETIMESTAMP Column In Derived Column Transformation

Mar 5, 2007

Hi,
I have dates in "mmddyy" format coming from the sources and they are older dates of mid 80s like 082580 for instance.

When I cast it this way (DT_DBTIMESTAMP) Source_Date , It says ok but throws a runtime error.

When I hardcode a date in same format, (DT_DBTIMESTAMP) "082580" , It becomes red (an indication of syntax error) . Please note that we use double quotes in expressions in Derived Column Transformation; So an anticipation that using double quotes over single ones would be the syntax problem would be wrong.



Any help in this will sincerely be appreciated.


Thanks

View 7 Replies View Related

How To Show Distinct Rows Of The Column Of The Dataset And Number Of Distinct Rows Of That Column

Mar 29, 2007

suppose i have aDataset with 11 rows. field1 with 5 rows of aaa, 6 rows of "bbb"

I want's some thing like

field1 rowcount
aaa 5
bbb 6

View 1 Replies View Related

How To Convert No. Of Rows To Variable

Jan 16, 2001

Help!!

I need to convert the number of rows returned from a column in a table & assign it to a variable, so I can use it to control a loop.
So far no joy.
The T-SQL used is :

SELECT COUNT(column_name) FROM table AS column_alias
GO
SET @numberofrows = column_alias
GO

Any suggestions? I have declared all variables etc.
The headache seems to be the converting from the column alias.

Thanks

W.

View 2 Replies View Related

Convert Rows To Colums

Aug 26, 2004

I am getting this from my client
TableA
StorID LineNumber Text
30000 1 ClientName
30000 2 ClientSurname
30000 3 3333333
30000 4 20-05-2004

I actually want this to look like
TableB
StoryID Name Surname Policy Date
30000 Name Surname 3333333 20-05-2004

Can you help with the script to convert this to one record with many fields (as in TableB) if this is

View 4 Replies View Related

How To Convert Rows Into Columns

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

How To Convert Rows Into Columns

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

Convert Rows To Colum

Aug 28, 2007

Hi All,

I'm having this many rows(around 25 columns am only showing some 3),
but in the output i want only 2 rows, how can i do that.

16_00SB3215-02
16_0100301A39A041
16_02Successful
16_03Successful
16_04Successful
16_0500301A39A042
18_00Pass
18_0200301A39A041
18_0300904BC1A1D6
18_325.18
18_335.19
18_345.24
18_365.40
18_375.39
18_385.32
18_40-49
18_41-56
18_42-53
18_44-62
18_45-63
18_46-60


26_00SB3216-02
26_0100301A39A041
26_02Successful
26_03Successful
26_04Successful
26_0500301A39A042
28_00Pass
28_0200301A39A041
28_0300904BC1A1D6
28_325.68
28_336.19
28_349.24
28_365.40
28_375.89
28_385.32
28_40-49
28_41-56
28_42-53
28_44-82
28_45-63
28_46-69

Output like this
----------------

16_00SB3215-02 6_0100301A39A0416_02Successful6_03Successful6_04Successful6_0500301A39A0428_00Pass8_0200301A39A0418_0300904BC1A1D68_325.18 8_335.19 8_345.24 ....................


like this

please help me, how can i do this

with Regards
Amjath

View 18 Replies View Related

How To Convert Columns To Rows?

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

Convert Rows To Columns

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

Convert Fields To Rows

Nov 19, 2007



I have a table (Not my own, cannot be changed) that has this structure:

Table1
ID, field, data
-----------------------
1, fname, john
1, lname, doe
1, address, 123 any st
2, fname, jack
2, lname, sprat
2 address, 345 some st
2, phone, 321-555-1234

I want this:

Table2
ID, fname, lname, address, phone
--------------------------------------------------
1, john, doe, 123 any st,,
2, jack, sprat, 345 some st, 321-555-1234

Notes:
This query will need to run on a regular schedule.
There are over 100 fields, I don't want to do this 100 times:


--UPDATE table2

--SET fname = table1.data
--FROM table2 INNER JOIN

-- table1 ON tablw2.ID = table1.ID

--WHERE table.field = fname



Any Ideas.

View 7 Replies View Related

How To Convert Rows To Columns

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

Convert Rows Into Columns

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

T-SQL (SS2K8) :: Way To Convert Rows To Columns

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

Want To Convert Columns Data Into Rows...

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

Convert Columns To Rows - Urgent

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

Transact SQL :: Convert Rows Into Columns?

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

Transact SQL :: Convert Rows To Columns?

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

Convert Columnar Data Into Rows

Nov 8, 2006

I have a table of data that needs to be converted to rows but with a way to identify for each row the column the row value came from.

Here's an example table:








Employee
FED
MEDI
SOCSEC

123
$100
$25
$70

234
$242
$54
$90

345
$75
$10
$55

Here's the format the data needs to be in after it's converted.








Employee
CodeType
Code
Amount

123
T
FED
$ 100

123
T
MEDI
$ 25

123
T
SOCSEC
$ 70

234
T
FED
$ 242

234
T
MEDI
$ 54

234
T
SOCSEC
$ 90

345
T
FED
$ 75

345
T
MEDI
$ 10

345
T
SOCSEC
$ 55

The CodeType will always be T.

This seems similar to some of the transpose-type samples but different enough (for me) to ask about.

Thank you for your help.

View 6 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved