Fetching data from sql database

Question

0.00/5 (No votes)

See more:

Hi All
Below I given the Table

SQL

Rack  cell  qty
A1     X     20
A1     Y     30
A1     Z     40
B1     X     70
B1     Y     60
C1     Z     34
C1     z     22

select A.Rack,
(select sum(B.Qty) from TestForAccess B  where B.Rack =A.Rack and B.Cell='x') as'X',
(select sum(B.Qty) from TestForAccess B  where B.Rack =A.Rack and B.Cell='y') as'Y',
(select sum(B.Qty) from TestForAccess B  where B.Rack =A.Rack and B.Cell='z') as'Z',
Count(A.Rack)
from TestForAccess A
group by A.Rack

after executing the query I am the table like below

SQL

Rack  X   Y   Z
A1    20  30  40
B1    70  60  Null
C1    Null Null 56

Now if I add another rack like

SQL

rack  cell   qty
A2     y      10

it is working fine it expected output

Rack  X   Y   Z
A1    20  30  40
A2   null 10  null >new line added
B1    70  60  Null
C1    Null Null 56

But if add another cell like

rack   cell    qty
A1      m       20

PROBLEM-
here cell m is new cell which is not give output like

Rack  X   Y   Z     m > this column should add which is Not giving
A1    20  30  40    20
A2   null 10  null  null
B1    70  60  Null  null
C1    Null Null 56  null

Any idea please help

Thanks

Posted 2-Aug-12 20:28pm

IndrajitDasgupat

Updated 2-Aug-12 20:44pm

v5

Add a Solution

Comments

Philip Stuyck 3-Aug-12 2:46am

m is not part of your query while x,y and z are. So it is only logical that m does not come out of your query.
Question is, are you expecting a lot more cell names here, because then you are going about this in a completely wrong way. You would need some advanced query techniques in that case like aggregating and pivoting data.

2 solutions

Solution 2

Hi,
Try the below approach.

CREATE TABLE #Temp
(
	Rack VARCHAR(50),
	cell VarchAR(50),
	qty INT
)
 
INSERT INTO #Temp
SELECT 'A1', 'X', 20 UNION
SELECT 'A1', 'Y', 30 UNION
SELECT 'A1', 'Z', 40 UNION
SELECT 'B1', 'X', 70 UNION
SELECT 'B1', 'Y', 60 UNION
SELECT 'C1', 'Z', 34 UNION
SELECT 'C1', 'z', 22 
UNION
SELECT 'A2', 'y', 10
UNION
SELECT 'A1', 'm', 20
 

 

--SELECT * FROM #Temp
 
/*Take the distinct cell values from #temp table and insert it into #temp2 table*/ 
SELECT DISTINCT IDENTITY(INT,1,1) AS ID , Cell INTO #Temp2 FROM #Temp
 
--SELECT * FROM #Temp2

 
 DECLARE @Count INT
 DECLARE @TotalRows INT
 DECLARE @ColName VARCHAR(MAX)
 DECLARE @SQL VARCHAR(MAX)
 
 
/*Table to show final result*/
CREATE TABLE #Temp3
(
	Rack VARCHAR(50)
)
 
/*Insert all the Rack values from #temp table to the final table*/
INSERT INTO #Temp3
SELECT DISTINCT Rack FROM #Temp
 

 SELECT @Count = 1, @TotalRows = COUNT(*) FROM #Temp2
 
 /*Loop through the rows of #temp2 table and add the cell values of table #temp2 as new column to table #temp3*/ 
WHILE @Count <= @TotalRows
BEGIN
 
SELECT @ColName = Cell FROM #Temp2 WHERE ID = @Count
 

 SET @SQL = 'ALTER TABLE #Temp3 ADD ' + @ColName + ' INT'
 PRINT @SQL
 EXECUTE (@SQL)
 
/*Once a new column is added set the values of the new column for the corresponding rack */
SET @SQL = 'UPDATE T3 SET '+ @ColName + ' = Val FROM #Temp T1'
SET @SQL = @SQL + ' inner join (select Rack, SUM(qty) AS Val FROM #Temp WHERE cell = '''+ @ColName +''' GROUP BY Rack) T ON T1.Rack = T.Rack'
SET @SQL = @SQL + ' INNER JOIN #Temp3 T3 ON T1.Rack = T3.Rack'
 
PRINT @Sql
Execute(@sql)
 
SET @Count = @Count + 1
END
 

 SELECT * FROM #Temp3
 
 
 
 DROP TABLE #TEMp
 DROP TABLE #TEMp2
 DROP TABLE #temp3

Posted 2-Aug-12 22:16pm

__TR__

Updated 3-Aug-12 2:06am

v4

Comments

IndrajitDasgupat 3-Aug-12 6:13am

in query analyser following errors getting I tried but abject point I am not getting

Server: Msg 170, Level 15, State 1, Line 65
Line 65: Incorrect syntax near '+'.

If possible please help

__TR__ 3-Aug-12 8:08am

Updated the solution to fix the above error. I am using sql server 2008. Looks like we can not use += in sql server 2000.

Add a Solution

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Philip Stuyck · Accepted Answer · 2012-08-02T21:15:00

Following query is shorter and more easily maintainable :

SQL

select *
from (select rack,cell,qty from TestForAccess) as D
pivot(sum(qty) for cell in ([X],[Y],[Z],[M])) as P;

you still have to know up front the cells though, I am looking for a solution for this without resorting to a stored procedure.

if you want flexibility this is the way to do it :

SQL

DECLARE @query NVARCHAR(4000)
DECLARE @cols NVARCHAR(2000)
SELECT  @cols = COALESCE(@cols + ',['+ cell + ']','['+ cell + ']') from (select distinct cell from TestForAccess) as T
print @cols
SET @query = 'select rack,'+@cols+' from (select rack,cell,qty from TestForAccess) as D pivot(sum(qty) for cell in ('+@cols+')) as P;'
print @query
execute(@query)

ok how to do this without pivot so that it works in sql 2000

SQL

select rack,
       SUM(case when cell='X' then qty end) as x,
       SUM(case when cell='Y' then qty end) as y,
       SUM(case when cell='Z' then qty end) as z,
       SUM(case when cell='M' then qty end) as m
from TestForAccess
group by rack

Fetching data from sql database

2 solutions

Solution 2

Solution 1

Add your solution here

Preview 0

Fetching data from sql database

2 solutions

Solution 2

Solution 1

Add your solution here

Preview 0

Existing Members

...or Join us