Get ROWs as COLUMNs - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Get ROWs as COLUMNs

Author

Topic

aluna98
Starting Member

1 Post

Posted - 2012-08-22 : 10:09:34

Hello

I'm using MS SQL 2008 R2, have three tables with following schema:

Table 1: Contains workshift info for each worker

CREATE TABLE workshift (
[ws_id] [bigint] NOT NULL,
[start_date] [datetime] NOT NULL,
[end_date] [datetime] NOT NULL,
[worker_id] [bigint] NOT NULL
)

INSERT INTO workshift VALUES (1, '2012-08-20 08:30:00', '2012-08-20 14:30:00', 1)
INSERT INTO workshift VALUES (2, '2012-08-20 14:30:00', '2012-08-20 22:30:00', 2)

Table 2: Contains monetary denominations

CREATE TABLE currency_denom (
[cd_id] [decimal](7, 2) NOT NULL,
[name] [nchar](100) NOT NULL
)

INSERT INTO currency_denom VALUES (1, '100.00')
INSERT INTO currency_denom VALUES (2, '50.00')
INSERT INTO currency_denom VALUES (3, '20.00')
INSERT INTO currency_denom VALUES (4, '10.00')
INSERT INTO currency_denom VALUES (5, '5.00')
INSERT INTO currency_denom VALUES (6, '1.00')

Table 3: Contains the quantity of each denomination the worker has received in every workshift

CREATE TABLE currency_by_workshift (
[cd_id] [decimal](7, 2) NOT NULL,
[ws_id] [bigint] NOT NULL,
[qty] [int] NOT NULL
)

INSERT INTO currency_by_workshift VALUES (1, 1, 1)
INSERT INTO currency_by_workshift VALUES (2, 1, 2)
INSERT INTO currency_by_workshift VALUES (3, 1, 2)
INSERT INTO currency_by_workshift VALUES (2, 2, 3)
INSERT INTO currency_by_workshift VALUES (4, 2, 4)
INSERT INTO currency_by_workshift VALUES (5, 2, 2)

I need to get the currency_by_workshift values in columns instead of rows, along with the workshift values, that is:

workshift |     workshift       |     workshift       | 100.00 | 50.00 | 20.00 | 10.00 | 5.00 | 1.00 
  ws_id   |     start_date      |     end_date        |        |       |       |       |      | 

    1     | 2012-08-20 08:30:00 | 2012-08-20 14:30:00 |    1   |   2   |   2   |   0   |   0  |   0
    2     | 2012-08-20 14:30:00 | 2012-08-20 22:30:00 |    0   |   2   |   0   |   4   |   2  |   0

I'm not able to use a case to count quantities for each currency denomination because they are configurable, if a new denomination is added, the query should be modified. Same applies if using PIVOT function, or I'm wrong?

How can I get the info that way?

Thank you

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-08-22 : 10:50:38

see

http://beyondrelational.com/modules/2/blogs/70/posts/10840/dynamic-pivot-in-sql-server-2005.aspx

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Jeff Moden
Aged Yak Warrior

652 Posts

Posted - 2012-08-25 : 21:21:22

quote:
I'm not able to use a case to count quantities for each currency denomination because they are configurable, if a new denomination is added, the query should be modified.

I'm curious... what demoninations do you think you might add?

--Jeff Moden

Andy Hyslop
Starting Member

14 Posts

Posted - 2012-08-28 : 06:02:23

[code]
DECLARE
@SQL VARCHAR(MAX)
,@COLUMNS VARCHAR(MAX)

SET @COLUMNS =
STUFF(( SELECT ',' + QUOTENAME([name]) AS [text()] FROM ( SELECT DISTINCT [name] FROM currency_denom) AS Code
FOR XML PATH('')),1,1,'' )

SET @SQL =
'SELECT *
FROM
( SELECT
W.[ws_id]
, W.[start_date]
, W.[end_date]
, CD.[Name]
, CBW.[Qty]

FROM workshift AS W

INNER JOIN currency_by_workshift AS CBW
ON W.[ws_id] = CBW.[ws_id]

INNER JOIN currency_denom AS CD
ON CBW.[cd_id] = CD.[cd_id]

) AS A
PIVOT
(
SUM([Qty]) FOR [name] IN (' + @COLUMNS + ')
) AS PIV'

EXEC (@SQL)

[/code]

Andy

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources