CODEWITHSUNDEEP
sqljoinrows
Erick Ely
Erick Ely

Reputation: 299

SQL turning rows into columns

I'm not exactly sure if what I'm needing is possible. I have two tables that are joined, one being a list of items, and one being when there was any movement for those items on specified date. The movements are specified by totalizers, in this example 1=Purchased, 2=Sold, 3=Adjusted. The coding I have so far gives me a separate row for each totalizer. I am needing to combine the rows into one for each item.

SELECT [Totalizer]=COALESCE(t2.[F1034],0)
      ,[UPC]=t1.[F01]
      ,[QTY]=sum(coalesce(t2.[F64],0))
      ,[Total Amount]=sum(COALESCE(t2.[F65],0))
FROM [STORESQL].[dbo].[COST_TAB] t1
LEFT OUTER JOIN [STORESQL].[dbo].[RPT_ITM_D] t2 
ON t1.F01=t2.F01 AND (F254='2011-10-1') and (F1034=1 or F1034=2 or F1034=3)
group by t1.F01,F1034
order by t1.F01

COST_TAB table consists of:

UPC
1
2
3
4

RPT_ITM_D Consists of the item movement:

UPC     Date       Totalizer     QTY     Total Amount
1       2011-10-1  1             1       9.00
1       2011-10-1  2             1       9.99
2       2011-10-1  1             2       6.00
2       2011-10-1  2             1       3.99
2       2011-10-1  3             1       3.00
3       2011-10-1  1             1       1.00

The SQL Code I have now results in:

UPC     Date       Totalizer     QTY     Total Amount
1       2011-10-1  1             1       9.00
1       2011-10-1  2             1       9.99
2       2011-10-1  1             2       6.00
2       2011-10-1  2             1       3.99
2       2011-10-1  3             1       3.00
3       2011-10-1  1             1       1.00
4       2011-10-1  0             0       0.00

I am needing it to result in:

UPC     Date       Purchased  AMT   Sold  AMT    Adjusted  AMT
1       2011-10-1  1          9.00  1     9.99   0         0.00
2       2011-10-1  2          6.00  1     3.99   1         3.00
3       2011-10-1  1          1.00  0     0.00   0         0.00
4       2011-10-1  0          0.00  0     0.00   0         0.00

I realize I will probably have to completely rework my columns, but I don't know where to start with this, or if I can even do it.

Upvotes: 1

Views: 1882

Answers (2)

Yahia
Yahia

Reputation: 70369

Assuming you are on MS SQL Server you can use PIVOT to achieve that - for explanation and some examples see http://msdn.microsoft.com/en-us/library/ms177410.aspx and http://www.simple-talk.com/community/blogs/andras/archive/2007/09/14/37265.aspx

Another option is to use subselects:

SELECT
T1.F01 AS UPC,
T2.F254 AS TheDate,
(SELECT SUM (COALESCE (X.F64, 0)) FROM [STORESQL].[dbo].[RPT_ITM_D] X WHERE X.F1034 = 1 AND X.F01 = T2.F254 AND X.F01 = T1.F01) AS PURCHASED,
(SELECT SUM (COALESCE (X.F65, 0)) FROM [STORESQL].[dbo].[RPT_ITM_D] X WHERE X.F1034 = 1 AND X.F01 = T2.F254 AND X.F01 = T1.F01) AS AMT_P,
(SELECT SUM (COALESCE (X.F64, 0)) FROM [STORESQL].[dbo].[RPT_ITM_D] X WHERE X.F1034 = 2 AND X.F01 = T2.F254 AND X.F01 = T1.F01) AS SOLD,
(SELECT SUM (COALESCE (X.F65, 0)) FROM [STORESQL].[dbo].[RPT_ITM_D] X WHERE X.F1034 = 2 AND X.F01 = T2.F254 AND X.F01 = T1.F01) AS AMT_S,
(SELECT SUM (COALESCE (X.F64, 0)) FROM [STORESQL].[dbo].[RPT_ITM_D] X WHERE X.F1034 = 3 AND X.F01 = T2.F254 AND X.F01 = T1.F01) AS ADJUSTED,
(SELECT SUM (COALESCE (X.F65, 0)) FROM [STORESQL].[dbo].[RPT_ITM_D] X WHERE X.F1034 = 3 AND X.F01 = T2.F254 AND X.F01 = T1.F01) AS AMT_A
FROM [STORESQL].[dbo].[COST_TAB] t1
LEFT OUTER JOIN [STORESQL].[dbo].[RPT_ITM_D] t2 
ON t1.F01=t2.F01 AND (F254='2011-10-1') and (F1034=1 or F1034=2 or F1034=3)
group by T1.F01, T2.F254
ORDER BY T1.F01, T2.F254

You should compare both option regarding performance/execution plan.

Upvotes: 3

zmilojko
zmilojko

Reputation: 2135

In your schema namespace/database you will find a definition of your table. The exact table storing table definitions varies depending which DBMS you are using. For instance, information_schema.COLUMNS would hold this info for you in MYSQL, and you can SELECT it from there like from any other table and cross those returned values in a join with your table.

Upvotes: 1

Related Questions