SQL rows to column

Question

This is my query.

select name, walk_to, count(*) count
from walk
where name='Rizwan'
group by name, walk_t0;

This results in data like:

NAME     WALK_TO     COUNT
Rizwan   Cafe        2
Rizwan   Arena       10
Rizwan   Outside     20

There are other users' data as well. What I want is:

NAME     CAFE     ARENA     OUTSIDE
Rizwan   2        10        20
John     3        21        90
James    5        5         9

Any idea how to write a query to get this result?

It is called a pivot, and the syntax varies by database type. — OldProgrammer, Mar 18 '15 at 20:07
there are ways to do this that are db independent -- I'll post and example bellow — Hogan, Mar 18 '15 at 20:17
@Hogan: The OP tagged the question as SQL Server-specific, so a vendor-independent example is irrelevant. Plus the question is a duplicate. But feel free to waste your time. — stakx - no longer contributing, Mar 18 '15 at 20:18
@stakx I was responding to the comments made by others which are miss-leading and wrong, but if you want to take my comment out of that context then feel free to waste your time. — Hogan, Mar 18 '15 at 20:20
@stakx also the duplicate you linked to is not very good, it only shows how to use pivot and is much more complicated than this question **and** does not give an example of using group by with case. — Hogan, Mar 18 '15 at 20:24
@Hogan: People will ask the same basic question ("How to transpose rows and columns in SQL?") again and again, only with ever slightly different example tables. Do we as a community really want to have to write the basically same answer over and over again? Wouldn't it be much more helpful to write a general answer that applies equally to *all* these specific instances of the problem? Yes, such an answer would be more complicated to understand; but the OPs would learn a valuable skill. — stakx - no longer contributing, Mar 19 '15 at 10:50

score 0 · Accepted Answer · edited May 23 '17 at 12:06

0

SELECT      name, 
            SUM(CASE WHEN walk_to = 'Cafe' THEN 1 ELSE 0 END) Cafe,
            SUM(CASE WHEN walk_to = 'Arena' THEN 1 ELSE 0 END) Arena, 
            SUM(CASE WHEN walk_to = 'Outside' THEN 1 ELSE 0 END) Outside
FROM        walk
GROUP BY    name

This is called pivioting for more examples look here SQL Server: Examples of PIVOTing String data

edited May 23 '17 at 12:06

Community

1
1

answered Mar 18 '15 at 20:18

legohead

530
2
8
23

Why post one example? Why not simply declare the question as a duplicate of the one you linked to? – stakx - no longer contributing Mar 18 '15 at 20:20
Sorry I'm new to stack overflow...just trying to help. How do I mark it as a duplicate? – legohead Mar 18 '15 at 20:21
1

No harm done. Re: how to mark a duplicate question, that is done via close votes. But you probably do not yet have enough reputation points for that. See the help pages. – stakx - no longer contributing Mar 18 '15 at 20:23
1

Oh ok, looks like I need 125 reputation points first but I'll definitely go through the help pages. Thanks for your help. – legohead Mar 18 '15 at 20:27

score 0 · Answer 2 · answered Mar 18 '15 at 20:18

0

select name,
       sum(case when walk_to='Cafe' then 1 else 0 end) as CAFE,
       sum(case when walk_to='Arena' then 1 else 0 end) as ARENA,
       sum(case when walk_to='Outside' then 1 else 0 end) as OUTSIDE
FROM walk
GROUP BY name

answered Mar 18 '15 at 20:18

Hogan

69,564
10
76
117

This assumes `walk_to` has a fixed number of values. – axblount Mar 18 '15 at 20:24
@axblount - all I have is the specifications by the OP to go on. If I have to guess at additional requirements I'd never be able to answer a question on SO. – Hogan Mar 18 '15 at 20:26

SQL rows to column

2 Answers2