Make a column from a query result

Trying to find some simple SQL Server PIVOT examples. Most of the examples that I have found involve counting or summing up numbers. I just want to pivot some string data. For example, I have a query returning the following.

Action1 VIEW   Action1 EDIT   Action2 VIEW   Action3 VIEW   Action3 EDIT   

I would like to use PIVOT (if even possible) to make the results like so:

Action1 VIEW EDIT   Action2 VIEW NULL   Action3 VIEW EDIT   

Is this even possible with the PIVOT functionality?

Replay

Remember that the MAX aggregate function will work on text as well as numbers. This query will only require the table to be scanned once.

SELECT Action,
       MAX( CASE data WHEN 'View' THEN data ELSE '' END ) ViewCol,
       MAX( CASE data WHEN 'Edit' THEN data ELSE '' END ) EditCol
 FROM t
 GROUP BY Action

If you specifically want to use the SQL Server PIVOT function, then this should work, assuming your two original columns are called act and cmd. (Not that pretty to look at though.)

SELECT act AS 'Action', [View] as 'View', [Edit] as 'Edit'
FROM (
    SELECT act, cmd FROM data
) AS src
PIVOT (
    MAX(cmd) FOR cmd IN ([View], [Edit])
) AS pvt

Table setup:

CREATE TABLE dbo.tbl (
    action VARCHAR(20) NOT NULL,
    view_edit VARCHAR(20) NOT NULL
);

INSERT INTO dbo.tbl (action, view_edit)
VALUES ('Action1', 'VIEW'),
       ('Action1', 'EDIT'),
       ('Action2', 'VIEW'),
       ('Action3', 'VIEW'),
       ('Action3', 'EDIT');

Your table: SELECT action, view_edit FROM dbo.tbl

Make a column from a query result

Query without using PIVOT:

SELECT Action,
[View] = (Select view_edit FROM tbl WHERE t.action = action and view_edit = 'VIEW'),
[Edit] = (Select view_edit FROM tbl WHERE t.action = action and view_edit = 'EDIT')
FROM tbl t
GROUP BY Action

Query using PIVOT:

SELECT [Action], [View], [Edit] FROM
(SELECT [Action], view_edit FROM tbl) AS t1
PIVOT (MAX(view_edit) FOR view_edit IN ([View], [Edit]) ) AS t2

Both queries result:
Make a column from a query result

Well, for your sample and any with a limited number of unique columns, this should do it.

select
    distinct a,
    (select distinct t2.b  from t t2  where t1.a=t2.a and t2.b='VIEW'),
    (select distinct t2.b from t t2  where t1.a=t2.a and t2.b='EDIT')
from t t1

From http://blog.sqlauthority.com/2008/06/07/sql-server-pivot-and-unpivot-table-examples/:

SELECT CUST, PRODUCT, QTY
FROM Product) up
PIVOT
( SUM(QTY) FOR PRODUCT IN (VEG, SODA, MILK, BEER, CHIPS)) AS pvt) p
UNPIVOT
(QTY FOR PRODUCT IN (VEG, SODA, MILK, BEER, CHIPS)
) AS Unpvt
GO

With pivot_data as
(
select
action, -- grouping column
view_edit -- spreading column
from tbl
)
select action, [view], [edit]
from   pivot_data
pivot  ( max(view_edit) for view_edit in ([view], [edit]) ) as p;

Category: sql server Time: 2008-08-23 Views: 1

Related post

  • How to Get the Sum (total) of a column in a query result set 2012-03-13

    Is there way that I can get the sum of a column in my query result set by just clicking on the column (something like in excel) so that i don't have to copy and paste the column into excel to the see the total sum of all the values in the column? I'm

  • Concatenate columns in SQL query result from a single table 2016-01-22

    I have query that looks something like the following: select firstname, lastname from names where firstname = 'john' I would like to have something like the following (conceptually): select [names.firstname + names.lastname] as 'fullname' from names

  • SELECT stuck giving results from the query result cache, though table has changed 2015-05-11

    A process that runs every two minutes or so started to misbehave. We noticed it was because the cursor fetched repeatedly 0 rows, even if indeed there were new rows satisfying the conditions in the where conditions. The select of the cursor is like t

  • How to get several specific records from a query result? 2015-05-14

    I know by using Like with "LIMIT 1, OFFSET x" I can get the Xth record from a returned query result. But what if I want to get X, Y, and Z all at once? Currently I do a for loop and it's like I'm doing this: SELECT record from table LIMIT 1, OFF

  • Get a list of one column from a SOQL result? 2015-06-13

    I have a SOQL query result that I use several times in a method. It pulls in many columns that I use for a few things. This includes a relationship column, that I do a query on another object. Is there a more elegant way to get at the relationship ID

  • Need help to construct query/pl-sql block to get DB table column name and query result as key-value pairs 2016-02-17

    I have a DB table has more than 50 columns. I am querying this table, it should return only one row any time. as below image from sqldeveloper. here I need to construct query/pl-sql block, to find out table column as a key and query result as values.

  • Any way to insert text on page from a query results? 2012-10-02

    I'm using Woocommerce and its atributes (custom taxonomies). I have some pages which I query this way: http://example.com/?tax1=term1&tax2=term2 This query gives me the posts from taxonomy 1 and taxonomy 2 that matches term1 and term2. I'd like to in

  • subquery uses ungrouped column from outer query 2016-01-19

    i've got 2 different SQL query that i would like to merge. server using PGSQL first one : SELECT queue.name AS service, COUNT(DISTINCT ticket.id) AS nb_ticket FROM ticket INNER JOIN queue ON ticket.queue_id = queue.id WHERE DATE(ticket.create_time) >

  • Sum SQL Server Count column from Grouped query 2016-01-27

    I am trying to write a query that takes all content from my database that has been rated higher than 3 stars and returns the top four modules based on star average and highest numbers of ratings. This part works great. But in order for me to put this

  • Make DATETIME column unique through query 2016-02-09

    I have the following problem: I have a table that has thousands of rows with all the same timestamp. I need every row to have a unique timestamp to be entered in a time series databases. So I am wondering if anyone has any ideas on how to make a DATE

  • How to add other column into pgrouting query result in pgAdmin output pane? 2014-01-20

    I'm working on my final research project now and I'm a new user in pgrouting. Is there any chances to add another column name to display in the output pane? I'd like to add road name column so that I can easily know the road, for the final result I'l

  • Copy a large data from sql query result 2014-02-09

    In my sql server 2008, after running the query the data return is very huge say in millions. I tried copying but it gives an exception sql memory error. Can you let us know how can i copy the entire data and paste it into excel. I don't want to expor

  • How to select a subset from a query result 2016-01-26

    I have a query which calculates the Time Difference and Distance Between the Next and Previous Row: SELECT * FROM ( SELECT iVehicleMonitoringId AS PreviousId, LEAD(iVehicleMonitoringId, 1) OVER (PARTITION BY iAssetID ORDER BY dtUTCDateTime) AS Curren

  • mysqli - find what column included the query result 2016-02-06

    I need to know how can I find from the below mysqli query that the result comes from which column, "source" or "target"? $results = $mysqli->query("SELECT source,target FROM dict WHERE MATCH (source,target) AGAINST ('%$word%' I

  • Splitting a csv column from select query into multiple columns 2014-07-24

    I have a stored procedure SELECT [ShipWorks].[dbo].[OrderItem].[Code] AS Code, CASE WHEN (LEN(ISNULL(MAX([ShipWorks].[dbo].[OrderItem].[Location]),'')) = 1) THEN MAX([ShipWorks].[dbo].[OrderItem].[Location]) ELSE MAX([Picklist].[dbo].[ItemData].[Inve

  • how to exclude one instance from SPARQL query result 2016-02-05

    I have a query to get some similar instances for a specific instance, which is owbes:Dies_Irae instance. This is the query: CONSTRUCT { ?recommendable0 ?predicate0 ?similarity0 } WHERE { ?recommendable0 ?predicate0 ?object0. owbes:Dies_Irae ?predicat

  • Encoding problems from MySQLdb query result on python 2016-02-11

    I'm using the library MySQLdb for Python to access a database with entries in Portuguese, with a bunch of accents, which I then save to an Excel file using xlsxwriter. When I'm closing the workbook to save it, I get the following error: UnicodeDecode

  • How to get specific portion from a query result 2016-06-19

    I am getting this JSON object as the result from a database query (as a variable). [{"BeneficiaryID":"2"},{"BeneficiaryID":"3"},{"BeneficiaryID":"4"},{"BeneficiaryID":"6"}] I

  • Access 2013:Trying to write macro to update form fields from a query result 2015-03-23

    I am trying to create a macro to update the value of a field in a form based off the results of a query. I am entirely new to working with vba in Access so I apologize if I am asking a basic question. I should mention, the "test" query returns e

iOS development

Android development

Python development

JAVA development

Development language

PHP development

Ruby development

search

Front-end development

Database

development tools

Open Platform

Javascript development

.NET development

cloud computing

server

Copyright (C) avrocks.com, All Rights Reserved.

processed in 1.341 (s). 13 q(s)