Fetch More Than One Column

How do I fetch multiple columns for use in a cursor loop?

Here is slightly modified version. Changes are noted as code commentary.

BEGIN TRANSACTION

declare @cnt int
declare @test nvarchar(128)
-- variable to hold table name
declare @tableName nvarchar(255)
declare @cmd nvarchar(500) 
-- local means the cursor name is private to this code
-- fast_forward enables some speed optimizations
declare Tests cursor local fast_forward for
 SELECT COLUMN_NAME, TABLE_NAME
   FROM INFORMATION_SCHEMA.COLUMNS 
  WHERE COLUMN_NAME LIKE 'pct%' 
    AND TABLE_NAME LIKE 'TestData%'

open Tests
-- Instead of fetching twice, I rather set up no-exit loop
while 1 = 1
BEGIN
  -- And then fetch
  fetch next from Tests into @test, @tableName
  -- And then, if no row is fetched, exit the loop
  if @@fetch_status <> 0
  begin
     break
  end
  -- Quotename is needed if you ever use special characters
  -- in table/column names. Spaces, reserved words etc.
  -- Other changes add apostrophes at right places.
  set @cmd = N'exec sp_rename ''' 
           + quotename(@tableName) 
           + '.' 
           + quotename(@test) 
           + N''',''' 
           + RIGHT(@test,LEN(@test)-3) 
           + '_Pct''' 
           + N', ''column''' 

  print @cmd

  EXEC sp_executeSQL @cmd
END

close Tests 
deallocate Tests

ROLLBACK TRANSACTION
--COMMIT TRANSACTION

Fetch more than one column

It is more effective to execute one fetch once.

$results = array();
if ($isQueryOk) {
      while ($row = $stmt->fetch(PDO::FETCH_ASSOC)) {
        array_push($result, $row['lastname_patient'].' '.$row['firstname_patient']);
      }
} else {
      trigger_error('Error executing statement.', E_USER_ERROR);
}

Fetch one data from column with multiple data

If the data in the column is structured consistently, you can define your own function to split the column into multiple columns:

DELIMITER $$
CREATE DEFINER=CURRENT_USER FUNCTION `SPLIT_STR`(
  x VARCHAR(255),     # Input
  delim VARCHAR(12),  # Delimiter
  pos INT             # Position or Field
) RETURNS varchar(255) CHARSET latin1
    DETERMINISTIC
BEGIN 
    RETURN REPLACE(SUBSTRING(SUBSTRING_INDEX(x, delim, pos),
       LENGTH(SUBSTRING_INDEX(x, delim, pos -1)) + 1),
       delim, '');
END$$
DELIMITER ;

Use it as follows:

SELECT SPLIT_STR(col1, ',', 2) FROM table; # Results in 'b'

Fetching elements from multiple columns of lists in a dataframe

IIUC explode with groupby

pd.concat([df[[x]].explode(x) for x in df.columns],axis=1)\
     .apply(lambda x : x.tolist(),axis=1).groupby(level=0).agg(list).tolist()
Out[366]: 
[[[1, 'a', 'aa'], [2, 'b', 'bb'], [3, 'c', 'cc']],
 [[4, 'd', 'dd'], [5, 'e', 'ee'], [6, 'f', 'ff']],
 [[7, 'g', 'gg'], [8, 'h', 'hh'], [9, 'i', 'ii']]]

Fetch data from multiple columns within the same table

done it myself i just added an OR condition at the end

$key=$_GET['key'];
                $key=str_replace("-"," ",$key);
                $query="select `rand_id`,`title_short`,`color`,`path`,`category` from `data` where `title_full` LIKE CONCAT('%',?,'%') OR `size` LIKE CONCAT('%',?,'%')";
                $stmt = $db->prepare($query);

                if($stmt){
                    $stmt->bind_param("ss",$key,$key);
                    $stmt->bind_result($rand_id,$title, $color, $path, $category);
                    $stmt->execute();

SQLite3 fetch multiple columns in one SELECT statement (python)

cur.fetchall() gives you a list of (name, age) tuples, as many as there are rows in your employees table. Your contains more than just two rows, so your name, age = ... syntax fails because that requires there to be two results. Even if there are only two rows, you'd assign those two rows (each a tuple) to the names name and age which is probably not what you wanted.

You probably want to iterate over the cursor; this lets you process each row tuple separately, including assigning the two column values to two separate targets:

for name, age in cur:
    # process each name and age separately

or just assign the cur.fetchall() result to a single variable:

results = cur.fetchall()

and process the list as needed.

MYSQL: Select Query with multiple values from one column

You need to GROUP BY BookID and a HAVING clause with the condition that both keywords are linked to that BookID:

SELECT b.BookID, b.Title 
FROM Books b
INNER JOIN KeywordAssignment ka ON ka.BookID = b.BookID
INNER JOIN Keyword k ON k.KeywordID = ka.KeywordID
WHERE k.Keyword IN ('Magic', 'Fantasy')
GROUP BY b.BookID, b.Title
HAVING COUNT(DISTINCT k.Keyword) = 2

This code will return books that are linked to both 'Magic' and 'Fantasy'.

If you want either of the 2 keywords then remove the HAVING clause.

---

Related Topics