473,321 Members | 1,708 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,321 software developers and data experts.

converting rows to columns

have a urgent requirement. Please somebody help me.

I have a table departinfo with following records

begin_time end_time Name Pieces

10:00 10:15 PopCorn 3
10:15 10:30 Biscuits 5
10:30 10:45 PopCorn 2

Now I need to run a sql query and the output should be as below :

begin_time end_time PopCorn Biscuits

10:00 10:15 3 0
10:15 10:30 0 5
10:30 10:45 2 0

Please note that only one column i.e. PopCorn is created in spite of
having multiple records in the table. Similarly the records are not
fixed. I mean that
there can be n number of records and the columns should be uniquely
created.
Can somebody help me out
PLZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZ ZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZ ZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZ
Jul 20 '05 #1
7 23483
Here's one solution:

CREATE TABLE MyTable
(
begin_time smalldatetime NOT NULL,
end_time smalldatetime NOT NULL,
Name varchar(10) NOT NULL,
Pieces int NOT NULL
CONSTRAINT PK_MyTable PRIMARY KEY
(
begin_time,
end_time,
Name
)
)

INSERT INTO MyTable
SELECT '10:00', '10:15', 'PopCorn', 3
UNION ALL SELECT '10:15', '10:30', 'Biscuits', 5
UNION ALL SELECT '10:30', '10:45', 'PopCorn', 2

SELECT
CONVERT(char(5), a.begin_time, 108) AS begin_time,
CONVERT(char(5), a.end_time, 108) AS end_time,
ISNULL(SUM(b.Pieces), 0) AS PopCorn,
ISNULL(SUM(c.Pieces), 0) AS Biscuits
FROM
(
SELECT DISTINCT
begin_time,
end_time
FROM MyTable
) AS a
LEFT JOIN MyTable b ON
b.begin_time = a.begin_time AND
b.end_time = a.end_time AND
b.Name = 'PopCorn'
LEFT JOIN MyTable c ON
c.begin_time = a.begin_time AND
c.end_time = a.end_time AND
c.Name = 'Biscuits'
GROUP BY
a.begin_time,
a.end_time
ORDER BY
a.begin_time
GO

--
Hope this helps.

Dan Guzman
SQL Server MVP

"Pooj" <po**********@hotmail.com> wrote in message
news:94**************************@posting.google.c om...
have a urgent requirement. Please somebody help me.

I have a table departinfo with following records

begin_time end_time Name Pieces

10:00 10:15 PopCorn 3
10:15 10:30 Biscuits 5
10:30 10:45 PopCorn 2

Now I need to run a sql query and the output should be as below :

begin_time end_time PopCorn Biscuits

10:00 10:15 3 0
10:15 10:30 0 5
10:30 10:45 2 0

Please note that only one column i.e. PopCorn is created in spite of
having multiple records in the table. Similarly the records are not
fixed. I mean that
there can be n number of records and the columns should be uniquely
created.
Can somebody help me out

PLZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZ ZZZZZZZZZZZZZZZZZZZZZZZZZZ
ZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZ ZZZZZZ
Jul 20 '05 #2
Hi

Check out a crosstab query that will transform your rows to columns:

http://support.microsoft.com/default...;EN-US;q175574

There are many posts about pivot tables or crosstab queries.. Search google
for more.

John

"Pooj" <po**********@hotmail.com> wrote in message
news:94**************************@posting.google.c om...
have a urgent requirement. Please somebody help me.

I have a table departinfo with following records

begin_time end_time Name Pieces

10:00 10:15 PopCorn 3
10:15 10:30 Biscuits 5
10:30 10:45 PopCorn 2

Now I need to run a sql query and the output should be as below :

begin_time end_time PopCorn Biscuits

10:00 10:15 3 0
10:15 10:30 0 5
10:30 10:45 2 0

Please note that only one column i.e. PopCorn is created in spite of
having multiple records in the table. Similarly the records are not
fixed. I mean that
there can be n number of records and the columns should be uniquely
created.
Can somebody help me out

PLZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZ ZZZZZZZZZZZZZZZZZZZZZZZZZZ
ZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZ ZZZZZZ
Jul 20 '05 #3
"John Bell" <jb************@hotmail.com> wrote in message news:<bt**********@titan.btinternet.com>...
Hi

Check out a crosstab query that will transform your rows to columns:

http://support.microsoft.com/default...;EN-US;q175574

There are many posts about pivot tables or crosstab queries.. Search google
for more.

John

"Pooj" <po**********@hotmail.com> wrote in message
news:94**************************@posting.google.c om...
have a urgent requirement. Please somebody help me.

I have a table departinfo with following records

begin_time end_time Name Pieces

10:00 10:15 PopCorn 3
10:15 10:30 Biscuits 5
10:30 10:45 PopCorn 2

Now I need to run a sql query and the output should be as below :

begin_time end_time PopCorn Biscuits

10:00 10:15 3 0
10:15 10:30 0 5
10:30 10:45 2 0

Please note that only one column i.e. PopCorn is created in spite of
having multiple records in the table. Similarly the records are not
fixed. I mean that
there can be n number of records and the columns should be uniquely
created.
Can somebody help me out

PLZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZ ZZZZZZZZZZZZZZZZZZZZZZZZZZ
ZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZ ZZZZZZ


Another solution would be using a union statement

select * from departinfo where popcorn = 0
union select * from departinfo where bisuits = 0
order by begin_time
Jul 20 '05 #4
Hello Dan,
Thanks for the quick response !
The solution you have provided wil not solve my problem due to
following reason :

The departinfo table records are not fixed as shown below. The time
difference can vary also the remainig data. The table will be
populated in the following format. Only the format is fixed the
records are not.

begin_datetime end_datetime Name Pieces

So my requirement is to convert the Name field into unique distinct
columns and match the pieces.
for example if the Name field contains items like Biscuits, Pizza,
PopCorn
then these many columns should be created and matched with the pieces.
Please help !
Thanks
Pooj


"Dan Guzman" <da*******@nospam-earthlink.net> wrote in message news:<8H**************@newsread1.news.atl.earthlin k.net>...
Here's one solution:

CREATE TABLE MyTable
(
begin_time smalldatetime NOT NULL,
end_time smalldatetime NOT NULL,
Name varchar(10) NOT NULL,
Pieces int NOT NULL
CONSTRAINT PK_MyTable PRIMARY KEY
(
begin_time,
end_time,
Name
)
)

INSERT INTO MyTable
SELECT '10:00', '10:15', 'PopCorn', 3
UNION ALL SELECT '10:15', '10:30', 'Biscuits', 5
UNION ALL SELECT '10:30', '10:45', 'PopCorn', 2

SELECT
CONVERT(char(5), a.begin_time, 108) AS begin_time,
CONVERT(char(5), a.end_time, 108) AS end_time,
ISNULL(SUM(b.Pieces), 0) AS PopCorn,
ISNULL(SUM(c.Pieces), 0) AS Biscuits
FROM
(
SELECT DISTINCT
begin_time,
end_time
FROM MyTable
) AS a
LEFT JOIN MyTable b ON
b.begin_time = a.begin_time AND
b.end_time = a.end_time AND
b.Name = 'PopCorn'
LEFT JOIN MyTable c ON
c.begin_time = a.begin_time AND
c.end_time = a.end_time AND
c.Name = 'Biscuits'
GROUP BY
a.begin_time,
a.end_time
ORDER BY
a.begin_time
GO

--
Hope this helps.

Dan Guzman
SQL Server MVP

"Pooj" <po**********@hotmail.com> wrote in message
news:94**************************@posting.google.c om...
have a urgent requirement. Please somebody help me.

I have a table departinfo with following records

begin_time end_time Name Pieces

10:00 10:15 PopCorn 3
10:15 10:30 Biscuits 5
10:30 10:45 PopCorn 2

Now I need to run a sql query and the output should be as below :

begin_time end_time PopCorn Biscuits

10:00 10:15 3 0
10:15 10:30 0 5
10:30 10:45 2 0

Please note that only one column i.e. PopCorn is created in spite of
having multiple records in the table. Similarly the records are not
fixed. I mean that
there can be n number of records and the columns should be uniquely
created.
Can somebody help me out

PLZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZ ZZZZZZZZZZZZZZZZZZZZZZZZZZ
ZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZ ZZZZZZ

Jul 20 '05 #5
Hi John,

Thanks for the quick reply.I checked the link u have provided but it
seems it
won't solve my problem b'coz DaypartInfo table records are not fixed.
They can vary E.g.my table format is as below:
begin_time end_time Name Pieces

10:00 10:15 PopCorn 3
10:15 10:30 Biscuits 5
10:30 10:45 PopCorn 2

In this table I want to convert the Nae field records as columns i.e.
Name field may contain PoCorn ,Pizza ,Biscuits,Cheese etc . Basically
these records can vary and those should be converted as columns at
runtime...

Please help....

Thanks,
Pooj.


"John Bell" <jb************@hotmail.com> wrote in message news:<bt**********@titan.btinternet.com>...
Hi

Check out a crosstab query that will transform your rows to columns:

http://support.microsoft.com/default...;EN-US;q175574

There are many posts about pivot tables or crosstab queries.. Search google
for more.

John

"Pooj" <po**********@hotmail.com> wrote in message
news:94**************************@posting.google.c om...
have a urgent requirement. Please somebody help me.

I have a table departinfo with following records

begin_time end_time Name Pieces

10:00 10:15 PopCorn 3
10:15 10:30 Biscuits 5
10:30 10:45 PopCorn 2

Now I need to run a sql query and the output should be as below :

begin_time end_time PopCorn Biscuits

10:00 10:15 3 0
10:15 10:30 0 5
10:30 10:45 2 0

Please note that only one column i.e. PopCorn is created in spite of
having multiple records in the table. Similarly the records are not
fixed. I mean that
there can be n number of records and the columns should be uniquely
created.
Can somebody help me out

PLZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZ ZZZZZZZZZZZZZZZZZZZZZZZZZZ
ZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZ ZZZZZZ

Jul 20 '05 #6
Hi

This may help...

http://www.sqlteam.com/item.asp?ItemID=2955

John
Jul 20 '05 #7
You can do this very easily and without any
sql coding with the RAC utility for S2k.
It can be used to generate dynamic crosstabs
and solve all types of problems in an easy way.
It's similar in concept to Access crosstab but
much more powerful.

RAC v2.2 and QALite @
www.rac4sql.net
Jul 20 '05 #8

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

9
by: Coleen | last post by:
Hi All :-) I found the way to get my column sum (Thanks Cor I did it a little different, but the result is what I wanted) I used: dt_stat_report_3b.Columns.Add(New DataColumn("Sum",...
4
by: sal | last post by:
Greets, All Converting array formula to work with datatables/dataset tia sal I finally completed a formula I was working on, see working code below. I would like to change this code so it...
1
by: Ramakrishnan Nagarajan | last post by:
Hi, I am converting Excel data into a Dataset in C#. There are around 24 columns in the Excel Sheet. First I tried to insert one row with correct values in the Excel sheet. i.e. for text columns...
13
by: ppateel | last post by:
Hi, I am new to c++ and I am converting a c program to c++. I changed malloc call to new and I am getting an exception violation. Here is the relevant piece of code. Compiler vc++ 7.0 (.Net...
21
by: py_genetic | last post by:
Hello, I'm importing large text files of data using csv. I would like to add some more auto sensing abilities. I'm considing sampling the data file and doing some fuzzy logic scoring on the...
1
by: praneethraj | last post by:
i have 2 rows with 3 columns each. ( each rows has a colun called ScoreType which contains different data like Performancelevel,scaledscore Subject Marks ScoreType A ...
2
by: truva | last post by:
Hi, I need a query which would convert Rows into Columns without causing any damages to the original data. I am not supposed to solve this by creating temporary tables and later dropping it. I...
9
by: Slain | last post by:
I need to convert a an array to a multidimensional one. Since I need to wrok with existing code, I need to modify a declaration which looks like this In the .h file int *x; in a initialize...
0
by: Orbie | last post by:
Hi Guys, I need some help with pivoting or converting some rows on a Table into columns using SQL Server 2008! I have a Table which contains the same Products in 4 different Stores. I'm only...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.