Re: [sqlite] Howto pivot in SQLite

[kumardsingh]

Follow below steps :Suppose My Table create table data (   ...> id int,  
...> countryid int,   ...> state varchar(30) ...> );Insert Query
sqlite> INSERT INTO data 
VALUES(56,9,'true');sqlite> INSERT INTO data  VALUES(56,54,'true');sqlite>
INSERT INTO data  VALUES (57,2,'false');sqlite> INSERT INTO data 
VALUES(57,9,'true')Now Pivot Query
---*SELECT  id,
GROUP_CONCAT(countryid,state) AS 'state' FROM data  GROUP BY id;*



--
View this message in context: 
http://sqlite.1065341.n5.nabble.com/Howto-pivot-in-SQLite-tp26766p79850.html
Sent from the SQLite mailing list archive at Nabble.com.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Howto pivot in SQLite

[Sam Carleton]

Tom,

Stop and think about what you just suggested: The invoice would look
something like this:

 |Qty | Desc  | Price   | Total
-++---+
+| 2  | 5x7   |  9.95   | 18.90
+| 1  | 5x7   |  9.95   | 18.90
+| 1  | 8x10  | 19.95   | 19.95
+| 2  | 16x20 | 49.00   | 98.00
+| 1  | CD| 99.95   | 99.95

Please make note that it looks strange that there are two line items with
5x7's.  Now you add the images, you get the following:

|Qty | Desc  | Imgs | Price   | Total
++---+---
| 2  | 5x7   | Img1 |  9.95   | 18.90
| 1  | 5x7   | Img2 |  9.95   | 18.90
 | 1  | 8x10  | Img2 | 19.95   | 19.95
 | 2  | 16x20 | Img9 | 49.00   | 98.00
| 1  | CD| Img1, Img2, Img3 | 99.95   | 99.95

In a perfect world, I would show the images, too.  That is coming, just not
in the initial release.  There is a lot to come, I just need to get this out
the door as quickly as possible while providing useful information to my
users.

Sam



On Sun, Jun 5, 2011 at 8:17 PM, BareFeetWare wrote:

> On 06/06/2011, at 8:30 AM, Sam Carleton 
> wrote:
>
> > allow the user to select the line and bring up a secondary dialog to
> manage the list of images
>
> You could simply execute a second select when the user asks for the set of
> images for that invoice. It's simpler and more accurate to then iterate
> through the returned rows than to parse a comma separated string.
>
> Tom
> BareFeetWare
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Howto pivot in SQLite

[BareFeetWare]

On 06/06/2011, at 8:30 AM, Sam Carleton  wrote:

> allow the user to select the line and bring up a secondary dialog to manage 
> the list of images

You could simply execute a second select when the user asks for the set of 
images for that invoice. It's simpler and more accurate to then iterate through 
the returned rows than to parse a comma separated string.

Tom
BareFeetWare
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Howto pivot in SQLite

[Sam Carleton]

On Sun, Jun 5, 2011 at 5:44 PM, Jay A. Kreibich  wrote:

> On Sun, Jun 05, 2011 at 12:47:47PM -0400, Sam Carleton scratched on the
> wall:
>
> > In one select statement, I want to return a view of all the Invoice_Items
> > for a particular Invoice such that there is one column that contains all
> the
> > image names in one string:
> >
> > Invoice_Item_Id | Invoice_Id | Description | Image Names
> > ++-+--
> > 1   |   1| CD  | Img1, Img2, Img3
> >
> > Can I do this with SQL?
>
>   As others have pointed out, you can, but that doesn't always make it
>  a good idea.  If you're doing this just to turn around and split that
>  value back up in your application code, you might want to rethink
>  your data handling.  The data representation in the database is clear
>  and correct.  Do you really want to alter that representation,
>  smashing the image names together into a single, less clear value, just
>  for the sake of making one query, rather than two?  Or even one query,
>  but with an extra line or two of code in the parse function?
>
>  Why not just deal with values in their native, and more correct,
>  "list of images" format?
>

Jay,

There is one simple reason:  Time

This is for my evening/weekend business where time is precious, I have been
dragging my feet on the current feature simply because I could not get my
head around how best to implement it.  It dawned on me today to take this
REALLY simple and less the idea approach for the general display of the
invoice and then to allow the user to select the line and bring up a
secondary dialog to manage the list of images.  It is quick and to the
point.  Once I get this in my customers heads, I will get feedback from them
for better ideas.

The bottom line is I am trying to NOT over engineer things and let my
customers drive things.  It seems to work well, they love seeing their
feedback taken to heart and I love their input:)

For now it is a good start:)

Sam
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Howto pivot in SQLite

[Jay A. Kreibich]

On Sun, Jun 05, 2011 at 12:47:47PM -0400, Sam Carleton scratched on the wall:

> In one select statement, I want to return a view of all the Invoice_Items
> for a particular Invoice such that there is one column that contains all the
> image names in one string:
> 
> Invoice_Item_Id | Invoice_Id | Description | Image Names
> ++-+--
> 1   |   1| CD  | Img1, Img2, Img3
> 
> Can I do this with SQL?

  As others have pointed out, you can, but that doesn't always make it
  a good idea.  If you're doing this just to turn around and split that
  value back up in your application code, you might want to rethink
  your data handling.  The data representation in the database is clear
  and correct.  Do you really want to alter that representation,
  smashing the image names together into a single, less clear value, just
  for the sake of making one query, rather than two?  Or even one query,
  but with an extra line or two of code in the parse function? 
  
  Why not just deal with values in their native, and more correct,
  "list of images" format?

   -j

-- 
Jay A. Kreibich < J A Y  @  K R E I B I.C H >

"Intelligence is like underwear: it is important that you have it,
 but showing it to the wrong people has the tendency to make them
 feel uncomfortable." -- Angela Johnson
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Howto pivot in SQLite

[Sam Carleton]

On Sun, Jun 5, 2011 at 1:04 PM, Simon Slavin  wrote:

>
> Take a look at the group_concat() function:
>
> http://www.sqlite.org/lang_aggfunc.html
>

That is PERFECT, thank you!  If the person who thought of this function
originally is reading this, thank you!!!  What a time saver!

Sam
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Howto pivot in SQLite

[Simon Slavin]

On 5 Jun 2011, at 5:47pm, Sam Carleton wrote:

> In one select statement, I want to return a view of all the Invoice_Items
> for a particular Invoice such that there is one column that contains all the
> image names in one string:
> 
> Invoice_Item_Id | Invoice_Id | Description | Image Names
> ++-+--
> 1   |   1| CD  | Img1, Img2, Img3

Take a look at the group_concat() function:

http://www.sqlite.org/lang_aggfunc.html

So just like you can use max(X), or total(X) in a SELECT, you can use 
group_concat(Image_Name) to string a bunch of returned values together, maybe 
something like

SELECT group_concat(Image_Name) FROM Image WHERE Invoice_Item_Id = 1

Simon.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users